Simple VBA column count

henrik2h

Board Regular
Joined
Aug 25, 2008
Messages
155
Office Version
  1. 2021
Platform
  1. Windows
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.

VBA Code:
    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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Maybe
VBA Code:
If Sheets("Flow_calculation").Range("AC228").End(xlToRight).Columns = 84 Then

But I should suggest
VBA Code:
If Sheets("Flow_calculation").Range("ZZ228").End(xlToLeft).Columns = 84 Then
 
Upvote 0
Maybe that is better but I still get an 1004-error on that line and I can't understand why
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
VBA Code:
    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
 
Upvote 0
Solution
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.
 
Upvote 0
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.)

VBA Code:
    ' 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
 
Upvote 0

Forum statistics

Threads
1,223,909
Messages
6,175,312
Members
452,634
Latest member
cpostell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top