CODEWITHSUNDEEP
excelvbams-access
0601D_K4BUM
0601D_K4BUM

Reputation: 13

Problem setting the range of non continuous range in vba access manipulating excel sheet

I am creating a Excel sheet from Access and I need to format the sheet from Access VBA, but when I try this piece of code it returns a error 1004:

Set rng = .Range("A2:E" & LastRow & "," & "J2:M" & LastRow)
With rng
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlVAlignCenter
End With

If I try it for each range it works: 

Set rng = .Range("A2:A" & LastRow)

I have this very same piece of code on a Excel spreadsheet and it works like a charm, but maybe from Access I should use another syntax: 

With DestWB.Sheets(strSheetName)
  .Range("B2:B" & MyLastRow - 3 & "," & "D2:D" & MyLastRow - 3 & "," & "H2:H" & MyLastRow - 3 & "," & "I2:I" & MyLastRow - 3).HorizontalAlignment = xlCenter
End With

Can someone point me how to apply the formatting to non continuous ranges on similar to this one?

Upvotes: 0

Views: 143

Answers (1)

mdelapena
mdelapena

Reputation: 185

Well, that's because you are assigning 2 ranges to a single range. you can either give 2 cell objects as parameters or a single string (just like in your working example).

I don't know what you are trying to do, but I would suggest to assign those 2 Ranges to 2 different variables, and then use those variables separately.

Set rng1 = .Range("A2:E" & LastRow)
Set rng2 = .Range("J2:M" & LastRow)

click here for more on how to use the Range object.

Upvotes: 1

Related Questions