CODEWITHSUNDEEP
excelvba
Mancho
Mancho

Reputation: 1

macro help to find LastRow IF

I have a macro to set LastRow by finding last empty row in column B. However, sometimes the entire row is empty, except for Row 1 where the headers are.
I need to set an IF statement on the LastRow variable to say IF Range B2 is NULL (or equals 0), then use B2 as parameter, to set LastRow. Else, find last row by .End(xlUp)

I've already tried different IF statements on my variable but all leads to an error message

Sub WeeklyGL()

  'Set Variable Parameters for Monthly Sheet
     Dim mySheet As String
     Dim LastrowMonth As Integer
     Dim mKey As Range
     Dim mKeyrng As Range
     Dim mValrng As Range

     mySheet = Sheets("Weekly_GL").Range("AE1")
     LastrowMonth = Sheets(mySheet).Range("B1048576").End(xlUp).Row --> this is where i need help
     Set mKey = Sheets(mySheet).Range("AC1")
     Set mKeyrng = Sheets(mySheet).Range("AC2:AC" & LastrowMonth)
     Set mValrng = Sheets(mySheet).Range("AD2:AD" & LastrowMonth)

Last try:

LastrowMonth = If Sheets(mySheet).Range("B2") ISNULL Then .Range("B2") Else Sheets(mySheet).Range("B1048576").End(xlUp).Row

Upvotes: 0

Views: 81

Answers (1)

Damian
Damian

Reputation: 5174

I've clean your code a bit with worksheet reference:

Option Explicit
Sub Test()

    'Set Variable Parameters for Monthly Sheet
     'Dim mySheet As String 'instead of this, create a worksheet variable like this:
    Dim mySheet As Worksheet
    Dim LastrowMonth As Integer
    Dim mKey As Range
    Dim mKeyrng As Range
    Dim mValrng As Range

    Set mySheet = ThisWorkbook.Sheets(ThisWorkbook.Sheets("Weekly_GL").Range("AE1")) 'this way you can reference and work cleaner:
    With mySheet
        LastrowMonth = .Cells(.Rows.Count, 2).End(xlUp).Row 'this is the standard to find the last row
        If LastrowMonth = 1 Then LastrowMonth = 2 'this is what you were looking for
        Set mKey = .Range("AC1")
        Set mKeyrng = .Range("AC2:AC" & LastrowMonth)
        Set mValrng = .Range("AD2:AD" & LastrowMonth)
    End With

End Sub

Option Explicit is a good choice to avoid errors because it force you to declare all your variables.

Using With allows you to reference what you are using, in this case mySheet just by using .

Upvotes: 1

Related Questions