Holey Data Range, Batman!

[CaliKidd]

I'm a novice VBA programmer and I've got an issue with an Excel VBA app that I'm working on. I'm not sure of the best way to solve it.

I have several columns (A:J) where I ask a user to populate the fields with data (starting on row 5). They can populate the worksheet by copying and pasting data from another worksheet or populate it manually (data entry). I have no idea of how many lines (rows) of data they will enter, so I am working on a way to "size up" how many rows of data they've entered. I am currently doing that by doing a COUNTA on range A5:A1048576 to get the total number of rows. With that result, I then have a macro that will loop through A5:J(COUNTA amount + 4) (the +4 is the offset since the data starts on row 5, not row 1. This macro looks for empty cells or other data errors that may throw my formulas off and reports them to the user so s/he can fix them.

If they populate every cell, then all is well and I can easily size up and scan the data for errors. However, if they leave any cells in Column A blank or skip entire rows, then it throws my count off.

I've thought about doing COUNTA's on the other columns (B:J) too and, if there are variances in the amounts, then using the one that returns the largest result, but that seems terribly inefficient. Furthermore, if those columns have empty cells in them also, those COUNTA totals will be off as well.

I tried using CurrentRegion, but received the following error message (even after I unprotected my worksheet):

Run-time error '1004':
Unable to get the CurrentRegion property of the Range class

Any ideas on a better way to approach this?

 

[Jonmo1]

Here's a pretty standard method for getting the last used row in a column..

lr = Cells(Rows.Count, "A").End(xlup).Row

Then use lr in your range

Range("A5:A" & lr)

No need to offset by 4, because the lr is correct regardless of data starting in row 5.


Hope that helps.

 

[CaliKidd]

I thought about that approach, but in the rare event the cell in column A of the last row happens to be left blank (and the other cells on the row are populated), then my count will be off by 1. I realize this is a slim possibility, but I'm looking for a way to ensure the count is spot on every time.

I suppose I could use your suggested solution and check the other columns (B:J) too and compare results, but it still seems a little cumbersome and I'm hoping there is a more efficient way.

 

[Jonmo1]

You could use

lr = Activesheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1

 

[Jonmo1]

Or even

lr = Range("A5").SpecialCells(xlLastCell).Row

 

[CaliKidd]

Thanks, I like the Activesheet.UsedRange.Rows.Count approach. It's short and sweet!