Forum Discussion

nlv22's avatar
nlv22
Copper Contributor
Oct 12, 2022

Not another 1/0/1900!

Hi all, 

I am trying to create a macro that will adjust to whatever number of rows needs to be averaged. I start off by calling in one sheet to another, then enter. However, instead of pulling in the 42 rows from the other sheet, it pulls in the 42 PLUS thousands of 1/0/1900. How do I get it to just pull in existing rows, but not lock it into a specific number as the number of rows will change with each use?

Thank you!

Excel
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Oct 12, 2022

    nlv22 

    Let's say you want to do something with cells in column A, starting in row 2. You could use

        Dim rng As Range
    Dim m As Long
    m = Range("A" & Rows.Count).End(xlUp).Row
    Set rng = Range("A2:A" & m)
    ' Do something with rng below
    ' ...

Resources