VBA Find a last row within a range?

Question

I want to find a last row with data within a range. for example i want to find a last empty row in range("A15:A42"). it should not go beyond A42. Any help.

lRow = Range("C15:C42").End(xlDown).Row   ....it goes beyond C42..

IMP Info copied from comments

yes ..i will have some other data from C43 ... – user2703472 3 hours ago

Maybe it's already done but you have to check your data first, are you sure that no value is present under line 42 in your column C ? — Guilhem Hoffmann
– Guilhem Hoffmann, Commented Nov 6, 2013 at 9:48
Also check if you are referencing in the correct sheet? the result of the code you posted vary depending on which sheet is selected. — L42
– L42, Commented Nov 6, 2013 at 9:56
Yes i tried with Activesheet.Range. but it goes beyond c42 and gives me a row nr like 1026 — user2703472
– user2703472, Commented Nov 6, 2013 at 9:58

Siddharth Rout · Accepted Answer · 2013-11-06 13:12:12Z

4

Is this what you are trying? I am assuming that C15 has data.

Option Explicit

Sub sample()
    Dim ws As Worksheet
    Dim rng As Range
    Dim LRow As Long

    '~~> Change this to the relevant sheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        Set rng = .Range("C15:C42")

        LRow = .Range(Split(.Cells(, rng.Column).Address, "$")(1) & _
               (rng.Row + rng.Rows.Count)).End(xlUp).Row

        Debug.Print LRow
    End With
End Sub

ScreenShot:

enter image description here

answered Nov 6, 2013 at 13:12

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Find a last row within a range?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related