Concatenate cells - error while running macro

Question

I am trying to concatenate cells as per below VBA script but it gives me run-time error '91' - Object variable or With Block variable not set. I guess that's smth to do with variable "x"? Can anyone help please?

Sub Macro1()

Dim Cells As Range
Dim x As Integer

x = 2

Do While Cells(x, 1) <> ""
Cells(x, 2) = Cells(x, 2) & Cells(x, 3) & Cells(x, 4)
Cells(x, 2) = ""
x = x + 1
Loop
Columns("B:B").EntireColumn.AutoFit

End Sub

Hi BigBen thanks you are always willing to help! That helped with error! Much appreciated! This macro should concatenate values from columns B,C,D, save them in column B and then delete the columns C & D, but it's only clears the contents starting from B2 and down the column :( do you have any idea on that? — Samuel Burton
– Samuel Burton, Commented Dec 3, 2020 at 15:02
Cells(x, 2) = "" - change that to Range("C" & x & ":D" & x).ClearContents. — BigBen
– BigBen, Commented Dec 3, 2020 at 15:06

BigBen · Accepted Answer · 2020-12-03 15:19:25Z

1

Putting comments into an answer:

Remove Dim Cells As Range, which is shadowing Cells from the Excel Object Model and causing the RTE 91.
Change Cells(x, 2) = " to Range("C" & x & ":D" & x).ClearContents.

answered Dec 3, 2020 at 15:19

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Concatenate cells - error while running macro

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related