VBA Squared Bracket Worksheet Reference

DKoontz

New Member
Joined
Mar 29, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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?

VBA Code:
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 so much! I'm still new to VBA but have been really enjoying learning.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Osvaldo Palmeiro

Well-known Member
Joined
Feb 24, 2009
Messages
635
Office Version
  1. 365
Platform
  1. Windows
Hi. Try to add the red part, as below.

Set FilterRange = [SalesData].Range("A1:Q" & [SalesData].Cells(Rows.Count, 1).End(xlUp).Row)
 
Solution

DKoontz

New Member
Joined
Mar 29, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi. Try to add the red part, as below.

Set FilterRange = [SalesData].Range("A1:Q" & [SalesData].Cells(Rows.Count, 1).End(xlUp).Row)
This worked! I thought I was covered with the first [SalesData] reference, didn't realize I needed another one for cells too. Thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,796
Messages
5,638,385
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top