VBA to move rows of data up removing blank rows.

[dbmathis]

Hi all,

I have the following senario.

Transaction Register Starting Dec-16-2005 Draft.xls
	K	L	M	N
10	Desciprion		Amount
11	#121 Tags		$177.64	0
12	#123 Truck		$279.89	0
13	#122 Emmy's Car		$280.00	0
14				0
15				0
16	Burger King		$6.90	0
17				0
18				0
19				0
20				0
21				0
22				0
23				0
24	St.Albert		$5.00	0
25	HEB		$49.06	0
26				0
27				0
28				0
29				0
30				0
31				0
Transaction Register

Due to the limitations of space on this forum I am only showing K11:M31 but my actual sheet has an area of K11:M41.

Does anyone know how to do the following with VBA? Basically move all blank lines to the bottom of the K11:M41 and move all rows that contain data to the top of the area?

The location of the rows of data within the area of K11:K41 varies from day to day depending on what's deleted, which is what creates these blank lines between data.

For example the end result would be:

Transaction Register Starting Dec-16-2005 Draft.xls
	K	L	M	N
10	Desciprion		Amount
11	#121 Tags		$177.64	0
12	#123 Truck		$279.89	0
13	#122 Emmy's Car		$280.00	0
14	Burger King		$6.90	0
15	St.Albert		$5.00	0
16	HEB		$49.06	0
17				0
18				0
Transaction Register

Thanks.

 

[Mark O'Brien]

It sounds like you may be deleting your data manually, in which case there might a simple solution for you with a tool that already exists.

There's a nice, free, Excel add-in called ASAP Utilities that has a function which does what you want. This is one of the mrexcel.com recommended add-ins. The utility is found here:

http://www.asap-utilities.com/


If you choose to download and install that add-in then here's how to solve your problem:

simply highlight the range K11:M41
then choose ASAP Utilities|Columns/Rows|Remove all empty rows

 

[dbmathis]

Thanks mark for the reply and I will look at that add-in, but for compatibilty issues I was trying to steer clear of add-ns.

Does anyone know how to accomplish this in VBA?

 

[dbmathis]

I also noticed that that add-in does delete the rows, but it deletes all empy rows in the entire column. I need this to be confined to K11:M41.

I guess I should not have used the term rows. Within K11:M41 I need all data that exists on the row within K11:M41 to be moved up together and all blank lines that exists on the row within K11:M41 to be moved down, excluding whatever else exists on the same row outside of the K11:M41 area.

I hope that makes sence.

Thanks for anyone who wants to try this code.

 

[jindon]

try

Sub test()
Range("k10:k41").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

 

[dbmathis]

Hi Jindon, thanks for the reply!

That deleted the entire rows. I am not looking to delete rows.

I have an area on my sheet, specifically K11:M41.

Any data that exists on a row that inclusively spans from K thru M columns needs to be moved up without deleting any rows or effecting any other data on the sheet that may exist on the same row.

The data that moves up should end at row 11 an in my first example.

---

This area lists bank transactions that have not cleared. As the clear, the data in this K11:M41 area is randomly removed leaving blank lines as in my first example. This is why I need the data that remains pushed up leaving room for new data on the bottom.

Hope this makes more sense.

 

[jindon]

try
sub test()
with range("K11:M41")
for i=.rows.count to 1 step -1
if isempty(.cells(i,1)) then .rows(i).delete shift:=xlup
next
end with
end sub

 

[dbmathis]

That is close! Is there any way that the blank lines can remain?

Here is a picture of the actual sheet and how the result should look.

 

[jindon]

Are there any cells that have formula in?

 

[dbmathis]

No formulas in K11:M41. There are formulas in cells all over the rest of the sheet.