How do you Find Number of Columns when a column label = ""?

  • Thread starter Thread starter Dennis
  • Start date Start date
D

Dennis

I have a row of column strings.

To find the number of columns in row 1 I would use:

Maxcol = sheets(1).Range("A1").End(xlToRight).Column

However, if one of the column labels before the end is "" (empty) then the
Maxcol stops there.

How do you find the true number of columns if some of the column labels are ""
but the last column label in the row exists?

Do you have to loop through all 256 possible columns in a for loop checking for
the end or is there a simpler way?

Thanks for any help.

Dennis
 
One way:

With Sheets(1).Cells(1, Columns.Count)
If IsEmpty(.Value) Then
maxcol = .Cells.End(xlToLeft).Column
Else
maxcol = Columns.Count
End If
End With
 
Thank you for the reply and code.

What would be the difference just using

For icol= 1 to 256
if Not IsEmpty(Sheets(1).Cells(1,icol).Value) Then maxcol=icol
next icol

or

For icol= 1 to 256
if Sheets(1).Cells(1,icol) <> "" Then maxcol=icol
next icol

Dennis
 
Using loops would be a lot slower. XL's End() method is optimized to
only evaluate the UsedRange, and is implemented in optimized
compiled code.
 
Dennis if you are looking for the last used column in a given row then
it would be:


Code:
 
Thanks Whisper. This works also.

I don't quite understand why

Maxcol = Sheets(1).Range("A2").End(xlToRight).Column

does not work when one of the labels in row 1 is "" and why

MaxCol = Sheets(1).Cells(1, Columns.Count).End(xlToLeft).Column

your code does work when one of the labels in row 1 is "" ?

Dennis
 
First, A2 is in row 2, not row 1. <bg>.

But try doing this yourself manually.

Put some data in A1:Z1. Then clear the contents of J1
Now put the cursor in A1 and hit the End key, then the right arrow key.

You'll stop way before the last used column (Z).

But if you go to the far right Cell IV1 and then End, then the Left arrow,
you'll stop at Z.

(And this supposes that you really mean an empty cell (not a formula that
evaluates to "").)

Those formulas that evaluate to "" (and even the cells that have been converted
to values) are not empty--even if they look it).
 
Thanks Whisper.

I don't quite understand the reasoning why

Maxcol = Sheets(1).Range("A2").End(xlToRight).Column

does not work when one of the labels in row 1 is "" and why

MaxCol = Sheets(1).Cells(1, Columns.Count).End(xlToLeft).Column

your code does work when one of the labels in row 1 is "" ?

Dennis
 
Maxcol = Sheets(1).Range("A2").End(xlToRight).Column
Click to expand...
This translates as Maxcol is equal to the count of filled cells
starting at Column 1 in Row 2. ( I assume the 2 is a typo)

Because it is starting from a filled cell it will keep going until it
finds a non-'filled' or empty cell
MaxCol = Sheets(1).Cells(1, Columns.Count).End(xlToLeft).Column
Click to expand...
In this case it is starting in the last column of the worksheet which
is normally empty, so it now looks backwards from the right until it
finds the first filled cell.

Try this out, put the code in the code page of a worksheet where you
have nothing in A1 or B1 but something in C1

Code:
--------------------

Sub FindColumn()
i = Sheets(1).Cells(1, Columns.Count).End(xlToLeft).Column
j = Sheets(1).Range("A1").End(xlToRight).Column
MsgBox ("Column number from the right is " & i)
MsgBox ("Column number from the left is " & j)
End Sub

--------------------

Now put something in E1

Hopefully you will now understand that it is basically looking for the
first instance where there is a change from its starting condition -
Empty looking for Not Empty and vice versa.

Best wishes

Gordon :)
 
Back
Top