Using VBA to reference the date in cell in a different sheet

[Captain_Conman]

Hello!

I am very new with VBA and am currently working on writing a macro that deletes all the rows in a sheet, that hold a date that falls on or before 6/1/18. Here is what I have so far...

Sub Macro2()
With ActiveSheet
.AutoFilterMode = False
With Range("g1", Range("g" & Rows.Count).End(xlUp))
.AutoFilter 1, "<=6/1/18"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With
End Sub

Everything works great, however, instead of typing the date into the code, I would like the macro to reference the date in the last row of column "G" on a sheet titled "Current". Any tips on how to make this reference would be appreciated! Thank you.

 

[James_Latimer]

I doubt this will be the best way of coding this, but try using...

.AutoFilter 1, "<=" & Sheets("Current").Range("G" & Sheets("Current").Cells(Sheets("Current").Rows.Count, "G").End(xlUp).Row).Text

 

[My Aswer Is This]

Try this:

Sub Macro2()
'Modified 6/14/18 10:30 AM EDT
Dim Lastrow As Long
Lastrow = Sheets("Current").Cells(Rows.Count, "G").End(xlUp).Row
ans = Sheets("Current").Cells(Lastrow, "G").Value
With ActiveSheet
.AutoFilterMode = False
With Range("g1", Range("g" & Rows.Count).End(xlUp))
    .AutoFilter 1, "<=" & ans
    counter = .Columns("G").SpecialCells(xlCellTypeVisible).Count
    If counter > 1 Then
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    Else
        MsgBox "No values found"
    End If
End With
.AutoFilterMode = False
End With
End Sub

 

[Captain_Conman]

THANK YOU SO MUCH!! It worked perfectly, which is an absolute day maker. I am so excited now! lol

THANK YOU THANK YOU THANK YOU!

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

THANK YOU SO MUCH!! It worked perfectly, which is an absolute day maker. I am so excited now! lol

THANK YOU THANK YOU THANK YOU!