# Excel - show which columns are used on any chosen worksheet

Go to solution Solved by water,

## Recommended Posts

yep, 1D array...

##### Share on other sites

Perfect.
I have modified the function so it works for a 1D array as well:

```#include <Excel.au3>

Global \$iSheet = 2
; create a Range holding all used cells including unused rows at the top and unused columns on the left
; See: https://www.autoitscript.com/wiki/Excel_Range#Used_Range
With \$oWorkbook.Sheets(\$iSheet)
\$oRangeLast = .UsedRange.SpecialCells(\$xlCellTypeLastCell)
\$oRange = .Range(.Cells(1, 1), .Cells(\$oRangeLast.Row, \$oRangeLast.Column))
EndWith
; Process the Range
Global \$aUsedRange = _Excel_RangeRead(\$oWorkbook, \$iSheet, \$oRange)
_ArrayDisplay(\$aUsedRange)
If UBound(\$aUsedRange, 0) = 2 Then
For \$iCol = 0 To UBound(\$aUsedRange, 2) - 1 ; Process all columns
For \$iRow = 0 To UBound(\$aUsedRange, 1) - 1 ; Process all rows
If \$aUsedRange[\$iRow][\$iCol] <> "" Then ; add checks for valid columns here
; ConsoleWrite(_Excel_ColumnToLetter(\$iCol + 1) & @CRLF)
ConsoleWrite(\$iCol + 1 & @CRLF)
ExitLoop ; Process next column
EndIf
Next
Next
Else
For \$iRow = 0 To UBound(\$aUsedRange, 1) - 1 ; Process all rows
If \$aUsedRange[\$iRow] <> "" Then ; add checks for valid columns here
; ConsoleWrite(_Excel_ColumnToLetter(\$iCol + 1) & @CRLF)
ConsoleWrite("A" & @CRLF)
ExitLoop
EndIf
Next
EndIf```

If  this works we will add a few lines to create an array with the column names.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
WebDriver - Wiki

##### Share on other sites

That works for a sheet with just one column, great.   Just need to sort the column name array now

##### Share on other sites

I think I've sorted the column name array.  Created an array with:

`Local \$ColumnInTab[0]`

Then:

`_ArrayAdd ( \$ColumnInTab, _Excel_ColumnToLetter(\$iCol + 1))`

and for the single column:

`_ArrayAdd ( \$ColumnInTab, "A")`

Thanks again for your help, what would I do without this forum!

## Create an account

Register a new account

• ### Recently Browsing   0 members

×

• Wiki

• Back

• #### Beta

• Git
• FAQ
×
• Create New...