CODEWITHSUNDEEP
excelvba
jdcaliff
jdcaliff

Reputation: 143

How to get row count and use in range

I am trying to get the number of rows in a spreadsheet and then use the number to select a range as such:

Dim lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("C1").Select
ActiveCell.Copy
Range("C2:C & lastrow").Select
ActiveSheet.Paste
Range("A2:A & lastrow").Select
ActiveSheet.Paste

So basically I am trying to copy content of C1 into every cell within columns A and C. However, using C:C and A:A in my Range statements causes extra rows to be added when I paste. So I really I need to be able to get the count of rows and then use that in the range. Can someone help me with this?

Thanks,

Upvotes: 0

Views: 1847

Answers (1)

Brandon Barney
Brandon Barney

Reputation: 2392

If I'm not mistaken, this code shouldnt compile. Your code is this:

Dim lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("C1").Select
ActiveCell.Copy
Range("C2:C & lastrow").Select ' <----
ActiveSheet.Paste
Range("A2:A & lastrow").Select ' <----
ActiveSheet.Paste

And it should be:

Dim lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("C1").Select
ActiveCell.Copy
Range("C2:C" & lastrow).Select
ActiveSheet.Paste
Range("A2:A" & lastrow).Select
ActiveSheet.Paste

This ignores the inefficiency of Select, Activate, Paste, etc, but it solves your problem (or at least should, unless I am really missing something).

Upvotes: 1

Related Questions