VBA to count the number of columns with data

[KevH]

Howdy all,

I need some VBA code to count the number of rows in a column that have data.


I want to use this number as a count in a for-next loop. I've got the for-next loop worked out, but I'm currently setting the count manually - not dynamicaly.

Do I need to set up a single-column dynamic Named Range and find the last row or is there an easier way to do this with VBA?

 

[Richard Schollar]

Hi

Assuming your data columns are contiguous (ie no entirely blank columns between them) and your data starts in the first column then you can count the number of columns with data from:

Sub NumCols()
Dim LastCol As Long
LastCol = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
End Sub

 

[KevH]

Sorry, the column should be F and it's contiguous with a header.

I'm playing with this to see if I can figure it out. Right now it's reading column one with 4 rows of non-contiguous data (spread over 18 actual rows) as 12.

Thanks for letting me know about "Find".

 

[Norie]

Here's another method of getting the last row.

LastRow = Range("F" & Rows.Count).End(xlUp).Row

Note there are other ways of looping through rows, you don't necessarily need the count.

 

[KevH]

Thanks Norie,

I think I'll run with your version. Here's a snippet of the code I fumbled into as a result.

Column = "F" 'In theory this column will not change
Count = GetLastRow(Column)

.....

Function GetLastRow(ByRef Column As String) As Integer

GetLastRow = Range(Column & Rows.Count).End(xlUp).Row

End Function

 

[Norie]

Kev

Glad to help.

But I think I need to point 2 things out.

1 Don't use a variable/parameter name like Column, that could clash with the VBA Column property.

2 Use Long instead of Integer. An integer can only hold values from -32,768 to 32,767. So if you have more than 32,767 rows of data the code will fail.

Now I know that might be unlikely, but what with Excel 2007 having 1m+ rows, who knows.

There's also another reason to use Long - Integer is converted to Long automatically by VBA.

 

[KevH]

Kev

Glad to help.

But I think I need to point 2 things out.

1 Don't use a variable/parameter name like Column, that could clash with the VBA Column property.

2 Use Long instead of Integer. An integer can only hold values from -32,768 to 32,767. So if you have more than 32,767 rows of data the code will fail.

Now I know that might be unlikely, but what with Excel 2007 having 1m+ rows, who knows.

There's also another reason to use Long - Integer is converted to Long automatically by VBA.

Thanks for the tip about "Column".

The column I'm keying off of is the Equipment number column. The equiment is large refigerators and freezers for research labs. Due to the limited number of industrial power sockets in the building, we will never reach Excels upper limit.

But if VBA converts anyway...

 

[Norie]

Well that's the thing, because it does convert you lose a miniscule bit of time.