VBA lastrow variable (excluding cells with no value but still have data validation)

[powercell99]

Hi,

I have a table: wrksht.ListObjects("DataTable1") , its table range is A1: X2000. All cells have data validation pop instructions on how to correctly enter data into each cell. After the table is populated by the user i want to re-size the table based on a lastrow variable identifying ONLY the last cell in column D that has an entry value. but every code i try erroneously identifies the last row that contains data validation, and not the last row with an entry value.

I've tried:
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).row
and
Cells(Rows.Count, 1).End(xlUp).Row

But they get stuck on the data-validation.

Ideally I'd like to delete all the data validation below the last row of data entry and re-size the table. Any suggestions?????

 

[Jerry Sullivan]

Hi powercell99,

You can use the Range.Find method to do that...

 With ActiveSheet.Columns("D")
   MsgBox "Last row: " & .Find("*", After:=.Cells(1), _
      LookIn:=xlValues, SearchDirection:=xlPrevious).Row
 End With

Change xlValues to xlFormulas if you want the code to find the last non-empty cell including those that have formulas that return a Null string value.

 

[Rick Rothstein]

Hi powercell99,

You can use the Range.Find method to do that...

 With ActiveSheet.Columns("D")
   MsgBox "Last row: " & [B][COLOR="#FF0000"].Find("*", After:=.Cells(1), _
      LookIn:=xlValues, SearchDirection:=xlPrevious[/COLOR])[/B].Row
 End With

Change xlValues to xlFormulas if you want the code to find the last non-empty cell including those that have formulas that return a Null string value.

Two points about the highlighted Find method call you posted...

1) You can omit the After:=.Cells(1) argument... the upper left corner of the range is the default cell when that argument is omitted and, since the search direction is xlPrevious, that default cell will always be the correct cell to start with.

2) I am pretty sure the SearchOrder argument, which you omitted, retains the last value that was specified for it (whether specified by code or by means of the Excel Find (or Replace) dialog box... for finding the last row, that argument needs to be this...

SearchOrder:=xlRows

in order to find the correct last row... so if you omit it, then the user could get an incorrect result if the data layout is "just right" and xlByColumns was specified the last time Find was used (either by code or by means of the Excel Find (or Replace) dialog box.

 

[Jerry Sullivan]

Hi Rick,

Good point that After:=.Cells(1) isn't needed.

I intentionally omitted SearchOrder:=xlRows because this has a one-column search range. If xlColumns was used in the previous search, I believe the same result would be returned. That being said providing SearchOrder:=xlRows as an example is better in case someone repurposes the code and changes the search range to be more than one column wide.

To be thorough, one should also set: SearchFormat:=False
I often do that, though it seems like overkill since very few users ever search by format.

Thanks for your comments.

 

[Rick Rothstein]

I intentionally omitted SearchOrder:=xlRows because this has a one-column search range. If xlColumns was used in the previous search, I believe the same result would be returned.

I did not even pay attention to the fact that you were applying Find to a single column. And yes, you are correct, the SearchOrder setting is immaterial for a single column (or a single row) search.

That being said providing SearchOrder:=xlRows as an example is better in case someone repurposes the code and changes the search range to be more than one column wide.

Agreed... and for the reason you stated.

To be thorough, one should also set: SearchFormat:=False
I often do that, though it seems like overkill since very few users ever search by format.

Yeah, I am pretty lax with specifying that setting as well. However, I use search by format (especially when using the Replace method) as I find it a very powerful option (if the circumstances are right)... but I do what I would hope all users of search by format would do... end the code with Application.FindFormat.Clear to make sure my settings do not live on after my code has ended. By the way, does it bother you as much as it does me that the method's property is named FindFormat but both the Find and Replace method arguments for it are named SearchFormat? I mean, for the Replace method, the name is ReplaceFormat in both locations... what was the Microsoft developer thinking when he/she chose to use two different names?!!?!?

 

[Jerry Sullivan]

By the way, does it bother you as much as it does me that the method's property is named FindFormat but both the Find and Replace method arguments for it are named SearchFormat? I mean, for the Replace method, the name is ReplaceFormat in both locations... what was the Microsoft developer thinking when he/she chose to use two different names?!!?!?

I hadn't noticed that inconsistency. That's a good one.