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

Resources