Macros tab selection

Copper Contributor

I have written a simple macro that deletes unwanted rows of data based on specific criteria.  I dont always want the macro to run on the same sheet.  how do I change it to reference the sheet I would select each time I run it.?  example below:

Kjstern1956_0-1677263833200.png

 

7 Replies

@Kjstern1956 

1) Delete all lines of the form

 

ActiveWindow.ScrollWorkbookTabs ...

 

and of the form

 

ActiveWindow.SmallScroll ...

 

They are side effects of recording a macro, but they aren't necessary and only slow down execution.

 

2) Delete all lines of the form

 

Sheets("...").Select

 

The code will then run on whichever sheet is active.

@Hans Vogelaar 

still working on the Macro.  I have several raw data sheets where I am trying to eliminate all non relevant rows.  not all raw data sheets have the same number of rows.

So when recording my Macro, i key:

Home

Shift-End Down-Shift-End Right and that captures all the data for the sheet i am in.  however when I stop recording and review the code, it show a specific cell range IE:  $A$1:$W$500.  

How do I correct the code so its not showing a specific cell reference.  

@Kjstern1956 

See if you can use

 

Range("A1").CurrentRegion

 

instead of

 

Range("A1:W500")

I get a compile Error & Syntax Error

@Kjstern1956 

Could you post the code (not a screenshot, but the text of the code)?

Here is the code. pretty simple Macro. I have several sheets with varying numbers of rows. I only want to keep the rows with the cost centers identified in the code.
Thanks

Sub Macro3()
'
' Macro3 Macro
' Step 1 for eliminating un-needed rows
'
' Keyboard Shortcut: Ctrl+Shift+F
'
Range("A1").Select
Selection.End(xlToRight).Select
ActiveSheet.Range("$A$1:$BU$10000").AutoFilter Field:=73, Criteria1:=Array( _
"01200042675", "01200042676", "01200042677", "01200042678", "03000002543", _
"03000034554"), Operator:=xlFilterValues
Range("A1").Select
End Sub

@Kjstern1956 

Does this work?

Sub Macro3()
'
' Macro3 Macro
' Step 1 for eliminating un-needed rows
'
' Keyboard Shortcut: Ctrl+Shift+F
'
    Dim LastRow As Long
    LastRow = Range("BU" & Rows.Count).End(xlUp).Row
    Range("A1:BU" & LastRow).AutoFilter _
        Field:=73, _
        Criteria1:=Array("01200042675", "01200042676", "01200042677", _
            "01200042678", "03000002543", "03000034554"), _
        Operator:=xlFilterValues
End Sub