(Solved) – Having trouble assigning a variable to the number of used rows


I’m pretty new to VBA in general. I’m working on a Macro in Excel to take the data from 2 different departments and putting them into the same sheet to use the data for pivot tables. The data is automatically separated onto different sheets by departments when I run the report. The problem I keep running into is the amount of data (rows used) is always changing.

I am trying to store a variable with the count of used rows for each department. Does anyone know if it’s even possible to use a variable to go to a specific row? An example would be if there were 213 rows being used by the first department then I’d want to paste the second departments data on row 214 to not overlap/miss any data for the pivot tables.

I’ve tried the following code and neither have worked.

Dim lastRow30A As Long
    lastRow30A.Value = .Cells(.Rows.count, "A").End(xlUp).Row

With this one I get a compile error of invalid or unqualified reference, and the .Rows in line 2 is highlighted.

Range(Selection, Selection.End(xlDown)).Select

Dim count30A As Long

count30A = Selection.count

With this one, it gets run-time error ‘438’: Object doesn’t support this property or method. The line Selection.count is highlighted.

Please let me know if anyone has any ideas or if anything wasn’t clear. Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *