Simple VBA column count

[henrik2h]

This should be really simple, what am I doing wrong? The line counting columns does not work. I want to start in the specific cell and count the number of columns used to the right. Could only be 84 or 44.

    If Sheets("Flow_calculation").Range("AC228").End(xlRight).Columns.Count = 84 Then
    Set PivotSource = Sheets("Flow_calculation").Range("AC228:DH275")
    
    k = 44 + 40  ' number of columns 
    
    Else
    
    Set PivotSource = Sheets("Flow_calculation").Range("AC228:BT275")
    k = 44  ' number of columns 
    
    End If

 

[Anthony47]

Maybe

If Sheets("Flow_calculation").Range("AC228").End(xlToRight).Columns = 84 Then

But I should suggest

If Sheets("Flow_calculation").Range("ZZ228").End(xlToLeft).Columns = 84 Then

 

[henrik2h]

Maybe that is better but I still get an 1004-error on that line and I can't understand why

 

[Anthony47]

Which instruction are you using?

 

[RoryA]

Range("AC228").End(xlRight) will return a reference to one cell, so applying Columns.Count to that will return 1. You should really be going from the last column on the sheet then working left to get the last used cell, then test its Column property to get the column number and subtract the column number of column AC less one from that to get the number of columns.

 

[Anthony47]

Hi RoryA; the OP reported he is "still getting" Error 1024, I suspect he is still using "xlRight" that is an enumeration that doesn't apply to End

 

[Alex Blakenburg]

In both your code and Anthony's suggestion you are just finding the last column on row 228 and getting the column number of that

    Dim colCount As Long
   
    ' Option 1 - Get first column and last column then calculate the no of columns
    With Sheets("Flow_calculation")
        colCount = .Range("AC228").End(xlToRight).Column - .Range("AC228").Column + 1
    End With
   
    ' Option 2 - Extend Range from AC228 to last column in row and get the Column Count
    With Sheets("Flow_calculation")
        colCount = .Range("AC228", .Range("AC228").End(xlToRight)).Columns.Count
    End With

PS: for the first option you could move the Range to the with statement but I thought this was easier to compare the 2 methods

 

[henrik2h]

Thank you!
Wasn't aware it only moved to the column and not taking into account the entire "range". Now it is working.

I tried the xlLeft (not reading properly) as proposed first but similar to my own statement I didn't use "xlToLeft", kind of stupid.. hence the error message.

 

[Alex Blakenburg]

Just to take up the point Rory and Anthony made that we normally prefer to get the last column by starting at the end of the sheet on the right and use xlToLeft, here are a couple of examples on how that might look.
(This gets you the last column even if there are some blank cells between the starting point (AC228) and the last non-blank cell on that row.)

    ' Option 1 - Get first column and last column then calculate the no of columns
    With Sheets("Flow_calculation")
        colCount = .Cells(228, Columns.Count).End(xlToLeft).Column - .Range("AC228").Column + 1
    End With
   
    ' Option 2 - Extend Range from AC228 to last column in row and get the Column Count
    With Sheets("Flow_calculation")
        colCount = .Range("AC228", .Cells(228, Columns.Count).End(xlToLeft)).Columns.Count
    End With