CODEWITHSUNDEEP
vbaexcel
knt5784
knt5784

Reputation: 129

Excel VBA delete empty cells and move contents up

I have data like this:

     xxxxx
xxxxx
     xxxxx
xxxxx
     xxxxx
xxxxx         
     xxxxx
xxxxx

I want to write a macro to go through each column from top to bottom, cut and paste the cells up. Here's my code working with the second column.

Dim x As Integer, y As Integer
x = 1
For y = 1 To 75
    If Cells(y, 2).Value <> "" Then
         Cells(y, 2).Cut
            Cells(x, 2).Paste
            x = x + 1
        End If
Next y

Excel keeps telling me: "Object doesn't support this property or method" on the Cells(x, 2).Paste

It doesn't run at all. Disregard the range of 75. I'm only have small data.

Upvotes: 0

Views: 273

Answers (1)

tea_pea
tea_pea

Reputation: 1542

Cells(x, 2).Paste isn't a valid command. use Cells(x, 2).PasteSpecial

try using intellisense to guide you to valid commands.

Upvotes: 1

Related Questions