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("A1").Select Range(Selection, Selection.End(xlDown)).Select Selection.count 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!