Find the last Cell in an Excel Column

[jayde]

I am trying to automate the processsing of a spreadsheet that I receive weekly. The spreadsheet is imported into access. My problem regards finding the "actual" last cell in column. I have two sets of data on a worksheet. A-D and F-I. I need to find the last row for each of the ranges. I have seen lots of code samples that find the last row and last column for a worksheet, but not for a range. The column B and Column G will contain data in the last row in each case. The matter is complicated by the fact that there is a blank "row" in the range when a category changes.

One picture is worth thousands of words so the data looks like so:

A                B   C                     D 
---------------------------------------------
Memory           
                 123 2100DDR256           100
                 345 2100DDR512           200
CPU
                 678 P4-2.0               155
                 901 p4-2.4               200

This pattern is the same for the range F - I

Any Ideas.....

Jayde Donnelly

 

[Zathras]

Assuming that columns "D" and "I" always are the longest in each of their respective groups, this is about the simplest way to get the row number of the last cell:

Option Explicit

Sub test()
Dim nLastD As Long
Dim nLastI As Long
  nLastD = LastRow("D")
  nLastI = LastRow("I")
  MsgBox "Last row for column D = " & nLastD
  MsgBox "Last row for column I = " & nLastI
End Sub

Function LastRow(Column As String) As Long
  LastRow = ActiveSheet.Range(Column & "65536").End(xlUp).Row
End Function