1

I've got a sheet called MachineData and I've got the following code

Worksheets("MachineData").Activate

'Last row with data on column AD (30)
lrow3 = Cells(Rows.Count, 30).End(xlUp).Row

The problem, is that lrow3 returns 2166 when it should return 1 because that's the last row on that column that contains data.

This column was added next to a table containing data that I extracted from a database. I believe that because the data was extracted from the database then this table has some weird formatting that makes this function not work.

My question is, how can I change the formatting of my table so this doesn't happen? or is there a way to change my code so this doesn't happen within this table?

Here is a picture of what my column looks like, anything below "File Name" is empty. I just get those green and white cell colours as this is part of the larger table.

enter image description here

Sam
  • 627
  • 2
  • 13
  • `lrow3 = Cells(Rows.Count, 30).End(xlUp).End(xlUp).Row` I avoid having tables in xlsm files for this very reason. – Spectral Instance May 17 '22 at 12:32
  • @SpectralInstance this worked! what is this exactly doing differently from my original code? – Sam May 17 '22 at 12:37
  • Your code is just finding the end of the table, this would be what you wanted _if your column wasn't full of blanks_, the 2nd `End(xlUp)` just goes up from the end of the table to the last non-empty cell. – Spectral Instance May 17 '22 at 12:39
  • @SpectralInstance thank you! if you post this as an answer I can select it as an answer to my problem. – Sam May 17 '22 at 12:40

2 Answers2

1

For your particular context, i.e. with blank cells at the end of your table

lrow3 = Cells(Rows.Count, 30).End(xlUp).End(xlUp).Row
Spectral Instance
  • 1,971
  • 2
  • 3
  • 6
0

Last Non-Blank Row Using the Find Method

  • It is assumed that the worksheet is neither filtered nor has hidden rows.
  • Blank means empty, ="", ', ...
  • Empty means empty.
  • Note that the only difference between the two procedures is the parameter of the 3rd Find method argument, called LookIn, set to xlValues (blank) or xlFormulas (empty).

Non-Blank

Sub LastNonBlankRow() ' is different than last non-empty row
    
    Const FirstCellAddress As String = "AD2"
    
    Dim wb As Workbook: Set wb = ThisWorkbook 'workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets("MachineData")
    Dim lRow As Long
    
    With ws.Range(FirstCellAddress)
        Dim lCell As Range
        Set lCell = .Resize(ws.Rows.Count - .Row + 1) _
            .Find("*", , xlValues, , , xlPrevious)
        If lCell Is Nothing Then Exit Sub ' no data in column
        lRow = lCell.Row
    End With
    
End Sub

Non-Empty

Sub LastNonEmptyRow()
    
    Const FirstCellAddress As String = "AD2"
    
    Dim wb As Workbook: Set wb = ThisWorkbook 'workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets("MachineData")
    Dim lRow As Long
    
    With ws.Range(FirstCellAddress)
        Dim lCell As Range
        Set lCell = .Resize(ws.Rows.Count - .Row + 1) _
            .Find("*", , xlFormulas, , , xlPrevious)
        If lCell Is Nothing Then Exit Sub ' no data in column
        lRow = lCell.Row
    End With
    
End Sub
VBasic2008
  • 44,888
  • 5
  • 17
  • 28