Return Row Number from Current Selection (Excel VBA)

Question

I am trying to process excel data dumped from quickbooks. In order to do what I want, I need to use first non blank row in column A, which varies depending on the period reported in quickbooks.

I am using

Sub Test()
Selection.End(xlDown).Select

to find the first non blank row in column A.

Say for example I have two files, one where the first non blank cell in column A is A157. Selection.End(xlDown).Select selects A157. I then need to select C1:C157.

The other spreadsheet has the first non blank cell in column A at A122. Selection.End(xlDown).Select selects A122. I then need to select C1:C122. The row in column A found using xlDown is a variable that I then need to use to create a selection in column C.

Any help is much appreciated. Thank you!

Say for example I have two files, one where the first row with information in column A is A157. Selection.End(xlDown).Select selects A157. I then need to select C1:C157. The other spreadsheet has the first row of information in column A at A122. Selection.End(xlDown).Select selects A122. I then need to select C1:C122. The row in column A found using xlDown is a variable that I then need to use to create a selection in column C. Does that clarify? — toucansame
Thanks for the clarification. Please look at the code examples I posted below, that should help. I am not sure how comfortable you are with VB, so let me know if you need more help or clarification. — Felix
Honestly, not super confident in VBA. 99% of what I know comes from poking around here. I will work with what you've posted and let you know if questions, but I probably won't be able to deep dive until next week. Thank you! — toucansame

Felix Felix · Accepted Answer · 2018-04-14T04:14:06

To get last row/column in a worksheet try:

    Dim ws As Worksheet
    set ws = ActiveSheet
    With ws
        lastRow = ws.Cells.SpecialCells(xlCellTypeLastCell).Row
        lastCol = ws.Cells.SpecialCells(xlCellTypeLastCell).Column
    End With

To get last row/column in a range try:

    Dim rg as Range
    With ws
         Set rg = .Range(.Cells(1, 3), .Cells(999, 3))
         With rg
             lastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
             lastCol = .Cells.SpecialCells(xlCellTypeLastCell).Column
         End With
    End With

To set new range for your last row/column try:

    With ws
         Set rg = .Range(.Cells(firstRow, firstCol), .Cells(lastRow, lastCol))
    End With

If you have more than 1 worksheet you can specify multiple sheets simply like this:

    Dim ws(3) As Worksheet
    set ws(0) = Worksheets("SheetName1");
    set ws(1) = Worksheets("SheetName2");
    'etc.

Return Row Number from Current Selection (Excel VBA)

2 Answers