VBA to count the number of columns with data

KevH

Board Regular
Joined
Apr 24, 2007
Messages
104
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?
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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:

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

KevH

Board Regular
Joined
Apr 24, 2007
Messages
104
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,230
Office Version
  1. 365
Platform
  1. Windows
Here's another method of getting the last row.
Code:
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

Board Regular
Joined
Apr 24, 2007
Messages
104

ADVERTISEMENT

Thanks Norie,

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

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,230
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Apr 24, 2007
Messages
104
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-known Member
Joined
Apr 28, 2004
Messages
76,230
Office Version
  1. 365
Platform
  1. Windows
Well that's the thing, because it does convert you lose a miniscule bit of time.:)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,328
Messages
5,600,985
Members
414,418
Latest member
mightyMagnus

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
Top