Mar 29 2021 02:22 PM
Hi Everyone,
I'm trying to figure out why this code runs when a button is placed on the active SalesData sheet but not when I move the button to an alternative dashboard sheet. I've been using previous code as reference while writing, and the last user used worksheet references with square brackets ie. [SalesData], could it be something with this syntax? The square brackets have been used to call other worksheets throughout the whole file and they work fine.
I wrote this to delete any rows older than 1 year old, which works, but only works when the macro button is placed on SalesData. Any ideas?
Sub DeleteOldRows()
Dim FilterRange As Range
Dim myDate As Date
Dim dte As Date
' Sets date to user input date and deletes anything older than 12 months from input date
dte = InputBox("Please Enter Date: (MM/DD/YYYY)", Default:=Format(Now, "mm/dd/yyyy"))
myDate = DateSerial(Year(dte), Month(dte) - 12, Day(dte))
' Set filter range and filter based on date
Set FilterRange = [SalesData].Range("A1:Q" & Cells(Rows.Count, 1).End(xlUp).Row)
FilterRange.AutoFilter Field:=16, Criteria1:="<=" & (myDate)
On Error Resume Next
' Delete filtered rows
With FilterRange
.Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
Err.Clear
[SalesData].AutoFilterMode = False
End Sub
Thank you! I'm loving my time spent with VBA so far.
Mar 29 2021 02:30 PM
SolutionIn the line
Set FilterRange = [SalesData].Range("A1:Q" & Cells(Rows.Count, 1).End(xlUp).Row)
Cells and Rows refer to the active sheet, so if that is a different sheet than SalesData, Cells(Rows.Count, 1).End(xlUp).Row will not return the correct value. Does it work if you change the above line to
With [SalesData]
Set FilterRange = .Range("A1:Q" & .Cells(.Rows.Count, 1).End(xlUp).Row)
End With
Note the . before Range, Cells and Rows.
Mar 29 2021 02:47 PM
Mar 29 2021 02:30 PM
SolutionIn the line
Set FilterRange = [SalesData].Range("A1:Q" & Cells(Rows.Count, 1).End(xlUp).Row)
Cells and Rows refer to the active sheet, so if that is a different sheet than SalesData, Cells(Rows.Count, 1).End(xlUp).Row will not return the correct value. Does it work if you change the above line to
With [SalesData]
Set FilterRange = .Range("A1:Q" & .Cells(.Rows.Count, 1).End(xlUp).Row)
End With
Note the . before Range, Cells and Rows.