Forum Discussion

Kjstern1956's avatar
Kjstern1956
Copper Contributor
Feb 24, 2023

Macros tab selection

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...
Macros and VBA
HansVogelaar's avatar
HansVogelaar
MVP
Feb 24, 2023

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.

Kjstern1956's avatar
Kjstern1956
Copper Contributor
Mar 01, 2023

HansVogelaar 

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.  

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Mar 01, 2023

    Kjstern1956 

    See if you can use

     

    Range("A1").CurrentRegion

     

    instead of

     

    Range("A1:W500")

    • Kjstern1956's avatar
      Kjstern1956
      Copper Contributor
      Mar 01, 2023
      I get a compile Error & Syntax Error
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Mar 01, 2023

        Kjstern1956 

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

Resources