VBA - Deleting Cells

[MichaelJ300]

Hello,

I need help on code that will find the last row of data, delete all cells, and move them to the left.

Thanks!

Please see below:
[TABLE="width: 532"]
<TBODY>[TR]
[TD="colspan: 5"]BEFORE</SPAN>[/TD]
[TD] [/TD]
[TD="colspan: 3"]After</SPAN>[/TD]
[/TR]
[TR]
[TD]1</SPAN>[/TD]
[TD]ABCD</SPAN>[/TD]
[TD][/TD]
[TD]Frank1</SPAN>[/TD]
[TD][/TD]
[TD] [/TD]
[TD]1</SPAN>[/TD]
[TD]ABCD</SPAN>[/TD]
[TD]Frank1</SPAN>[/TD]
[/TR]
[TR]
[TD]2</SPAN>[/TD]
[TD]EFGH</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD]Frank2</SPAN>[/TD]
[TD] [/TD]
[TD]2</SPAN>[/TD]
[TD]EFGH</SPAN>[/TD]
[TD]Frank2</SPAN>[/TD]
[/TR]
[TR]
[TD]3</SPAN>[/TD]
[TD][/TD]
[TD]IJKL</SPAN>[/TD]
[TD][/TD]
[TD]Frank3</SPAN>[/TD]
[TD] [/TD]
[TD]3</SPAN>[/TD]
[TD]IJKL</SPAN>[/TD]
[TD]Frank3</SPAN>[/TD]
[/TR]
[TR]
[TD]4</SPAN>[/TD]
[TD][/TD]
[TD]MNOP</SPAN>[/TD]
[TD][/TD]
[TD]Frank4</SPAN>[/TD]
[TD] [/TD]
[TD]4</SPAN>[/TD]
[TD]MNOP</SPAN>[/TD]
[TD]Frank4</SPAN>[/TD]
[/TR]
[TR]
[TD]5</SPAN>[/TD]
[TD]QRST</SPAN>[/TD]
[TD][/TD]
[TD]Frank5</SPAN>[/TD]
[TD][/TD]
[TD] [/TD]
[TD]5</SPAN>[/TD]
[TD]QRST</SPAN>[/TD]
[TD]Frank5</SPAN>[/TD]
[/TR]
[TR]
[TD]6</SPAN>[/TD]
[TD][/TD]
[TD]UVWX</SPAN>[/TD]
[TD]Frank6</SPAN>[/TD]
[TD][/TD]
[TD] [/TD]
[TD]6</SPAN>[/TD]
[TD]UVWX</SPAN>[/TD]
[TD]Frank6</SPAN>[/TD]
[/TR]
[TR]
[TD]7</SPAN>[/TD]
[TD]XZ12</SPAN>[/TD]
[TD][/TD]
[TD]Frank7</SPAN>[/TD]
[TD][/TD]
[TD] [/TD]
[TD]7</SPAN>[/TD]
[TD]XZ12</SPAN>[/TD]
[TD]Frank7</SPAN>[/TD]
[/TR]
[TR]
[TD]8</SPAN>[/TD]
[TD][/TD]
[TD="align: right"]3456</SPAN>[/TD]
[TD]Frank8</SPAN>[/TD]
[TD][/TD]
[TD] [/TD]
[TD]8</SPAN>[/TD]
[TD="align: right"]3456</SPAN>[/TD]
[TD]Frank8</SPAN>[/TD]
[/TR]
[TR]
[TD]9</SPAN>[/TD]
[TD][/TD]
[TD="align: right"]7890</SPAN>[/TD]
[TD]Frank9</SPAN>[/TD]
[TD][/TD]
[TD] [/TD]
[TD]9</SPAN>[/TD]
[TD="align: right"]7890</SPAN>[/TD]
[TD]Frank9</SPAN>[/TD]
[/TR]
[TR]
[TD]10</SPAN>[/TD]
[TD="align: right"]1234</SPAN>[/TD]
[TD][/TD]
[TD]Frank10</SPAN>[/TD]
[TD][/TD]
[TD] [/TD]
[TD]10</SPAN>[/TD]
[TD="align: right"]1234</SPAN>[/TD]
[TD]Frank10</SPAN>[/TD]
[/TR]
[TR]
[TD]11</SPAN>[/TD]
[TD][/TD]
[TD="align: right"]5678</SPAN>[/TD]
[TD][/TD]
[TD]Frank11</SPAN>[/TD]
[TD] [/TD]
[TD]11</SPAN>[/TD]
[TD="align: right"]5678</SPAN>[/TD]
[TD]Frank11

</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL span=5><COL><COL span=3></COLGROUP>[/TABLE]

 

[swaink]

Hi MichaelJ300

Are you familiar with macros?

One suggestion would be to record the macro as a starting point

What I would then do is highlight the area of cell in your table

Then using the F5 key I would select only the blank cells

then all you would need to do is delete cells left

Kevin

 

[MichaelJ300]

Thanks Kevin!

What I did and maybe I should have tried this the first time. I went to go to special grab blanks and then shifted to the left. Now I will try your idea and create a record a macro to see if this will work.

Thanks again,

MJ

 

[MARK858]

I can't see from your question why you need to find the last row of data as

Cells.SpecialCells(4).Delete Shift:=xlToLeft

should do.

But if you do actually need to find the last row then I suppose either

ActiveSheet.UsedRange.SpecialCells(4).Delete Shift:=xlToLeft

or

Sub DelBlank()

    Dim lr As Long, lc As Long

    lr = Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
    lc = Cells.Find(What:="*", SearchOrder:=xlColumns, SearchDirection:=xlPrevious, LookIn:=xlValues).Column

    Range(Cells(1, 1), Cells(lr, lc)).SpecialCells(4).Delete Shift:=xlToLeft

End Sub

should do.

All the codes above only work if your cells are truly blank.