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:

BEFORE</SPAN>		After</SPAN>
1</SPAN>	ABCD</SPAN>		Frank1</SPAN>			1</SPAN>	ABCD</SPAN>	Frank1</SPAN>
2</SPAN>	EFGH</SPAN>			Frank2</SPAN>		2</SPAN>	EFGH</SPAN>	Frank2</SPAN>
3</SPAN>		IJKL</SPAN>		Frank3</SPAN>		3</SPAN>	IJKL</SPAN>	Frank3</SPAN>
4</SPAN>		MNOP</SPAN>		Frank4</SPAN>		4</SPAN>	MNOP</SPAN>	Frank4</SPAN>
5</SPAN>	QRST</SPAN>		Frank5</SPAN>			5</SPAN>	QRST</SPAN>	Frank5</SPAN>
6</SPAN>		UVWX</SPAN>	Frank6</SPAN>			6</SPAN>	UVWX</SPAN>	Frank6</SPAN>
7</SPAN>	XZ12</SPAN>		Frank7</SPAN>			7</SPAN>	XZ12</SPAN>	Frank7</SPAN>
8</SPAN>		3456</SPAN>	Frank8</SPAN>			8</SPAN>	3456</SPAN>	Frank8</SPAN>
9</SPAN>		7890</SPAN>	Frank9</SPAN>			9</SPAN>	7890</SPAN>	Frank9</SPAN>
10</SPAN>	1234</SPAN>		Frank10</SPAN>			10</SPAN>	1234</SPAN>	Frank10</SPAN>
11</SPAN>		5678</SPAN>		Frank11</SPAN>		11</SPAN>	5678</SPAN>	Frank11 </SPAN>

<TBODY>
</TBODY><COLGROUP><COL span=5><COL><COL span=3></COLGROUP>

 

[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.