CODEWITHSUNDEEP
excelformsvba
vividillusion
vividillusion

Reputation: 99

Copying down a range from cells above in VBA

I have a formula that automatically populates cells by copying down from above and the following code works perfectly for the cell references that I require.

Dim lstRw As Long
Dim Rng As Range
lstRw = Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = Range("A" & lstRw + 1)
Range("A" & lstRw & ":C" & lstRw & ":I" & lstRw & ":N" & lstRw).Copy Range("A" & lstRw + 1)
With Rng

However.. I also want the code to copy down a range from AW:BK..

I have tried the following:

Dim lstRw As Long
Dim Rng As Range
lstRw = Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = Range("A" & lstRw + 1)
Range("A" & lstRw & ":C" & lstRw & ":I" & lstRw & ":N" & lstRw & "AW:BK" & lstRw).Copy Range("A" & lstRw + 1)
With Rng

But I keep getting compile errors!

Regards

Upvotes: 1

Views: 1366

Answers (1)

Bill
Bill

Reputation: 34

Change "AW:BK" to "AW" & lstRw & ":BK" & lstRw

Say your last row is row 10, to reference multiple columns of row 10 you would have to write AW10:BK10 your code just needs to replicate this.

EDIT I only looked at the AW:BK portion initially. If you are trying to select columns A,C,I,N,AW:BK of the same row you need to separate them by commas not colons

range("A" & LstRw & ",C" & LstRw & ",I" & LstRw & ",N" & LstRw & ",AW" & LstRw & ":BK" & LstRw)

Upvotes: 2

Related Questions