1

Excel Spreadsheet: 

        A            B               C             D          E           F 
1
2
3               List_A            List_B
4               Product A    
5               Product B   
6                                 Product A
7               Product C         Product B
8       
9               Product D         Product C
10              Product E   
11                                Product D
12                                Product E
13
14

In the above Excel spreadsheet I have list of products in Column A and in Column C.
Both lists contain empty cells between the values in the column.

Now, I want to identify the last non-empty row in both lists:
In Column B this would be 10
In Column C this would be 12

Therefore, I tried to go with this VBA:

Sub Identify_last_Row()
Last_Row_Number_Column_B = (Sheet1.Cells(Tabelle3.Range("B3").Row, Sheet1.Range("B3").Column).End(xlDown).Row)
Last_Row_Number_Column_C = (Sheet1.Cells(Tabelle3.Range("C3").Row, Sheet1.Range("C3").Column).End(xlDown).Row)
Sheet1.Range("D1").Value = Last_Row_Number_Column_B
Sheet1.Range("E1").Value = Last_Row_Number_Column_C
End Sub

This query goes through but it does not ignore the empty cells between the values in the column.

How do I have to modify the code to identfy the last non-empty row and ignore the empty cells between the values in the column?

Michi
  • 4,663
  • 6
  • 33
  • 83
  • Maybe try like this : Last_Row_Number_Column_B = Sheet1.Range("B1000000").end(xlup).row – karma May 06 '20 at 12:27
  • @karma for your interest `B1000000` is not the last cell of column B (see my answer below) and therefore might fail. Never hardcode the row number if you can determine it by code. – Pᴇʜ May 06 '20 at 12:28
  • 1
    @Pᴇʜ, Thank you for the correction. I realize that the code will fail if (A) the last row is not "B1000000" and/or (B) the Excel is an older version which has 65536 rows. Thank you once again, PEH. – karma May 06 '20 at 12:43

1 Answers1

1

You need to use xlUp outgoing from the very last cell in the column:

.Cells(.Rows.Count, "B")

is the very last cell of column B and .End(xlUp) will move upwards until data is found.

With Sheet1 'your worksheet
    Last_Row_Number_Column_B = .Cells(.Rows.Count, "B").End(xlUp).Row
    Last_Row_Number_Column_C = .Cells(.Rows.Count, "C").End(xlUp).Row
End With
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73