Determine Last Cells (row & column number) Containing Data:
Return to List
' WORTH IT'S WEIGHT IN GOLD !!!! ' Because once you have the information (below)-- you can easily cycle
through and read all of the cells in the worksheet, with a routine such as: For R = 1 to LastRowWithData
For C = 1 to LastColWithData
strCellValue = Cells(R, C).Value' Value of Cell at Row & Column shown strCellFormula = Cells(R, C).Formula' or formula Next C
Next R
Public Sub LastCellsWithData()
' ExcelLastCell is what Excel thinks is the last cell Set ExcelLastCell = ActiveSheet.Cells.SpecialCells(xlLastCell)
' Determine the last row with data in it (must also copy above para for this to work) LastRowWithData = ExcelLastCell.Row
Row = ExcelLastCell.Row
Do While Application.CountA(ActiveSheet.Rows(Row)) = 0 And Row <> 1
Row = Row - 1
Loop
LastRowWithData = Row' Row number
' Determine the last column with data in it (must also copy the top para for this to work) LastColWithData = ExcelLastCell.Column
Col = ExcelLastCell.Column
Do While Application.CountA(ActiveSheet.Columns(Col)) = 0 And Col <> 1
Col = Col - 1
Loop
LastColWithData = Col' Column number End Sub