VBA to move rows of data up removing blank rows.

dbmathis

Well-known Member
Joined
Sep 22, 2002
Messages
1,064
Hi all,

I have the following senario.
Transaction Register Starting Dec-16-2005 Draft.xls
KLMN
10DesciprionAmount
11#121 Tags$177.640
12#123 Truck$279.890
13#122 Emmy's Car$280.000
140
150
16Burger King$6.900
170
180
190
200
210
220
230
24St.Albert$5.000
25HEB$49.060
260
270
280
290
300
310
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
KLMN
10DesciprionAmount
11#121 Tags$177.640
12#123 Truck$279.890
13#122 Emmy's Car$280.000
14Burger King$6.900
15St.Albert$5.000
16HEB$49.060
170
180
Transaction Register


Thanks.
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Mark O'Brien

MrExcel MVP
Joined
Feb 15, 2002
Messages
3,530
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

Well-known Member
Joined
Sep 22, 2002
Messages
1,064
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

Well-known Member
Joined
Sep 22, 2002
Messages
1,064
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

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

dbmathis

Well-known Member
Joined
Sep 22, 2002
Messages
1,064
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

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

Well-known Member
Joined
Sep 22, 2002
Messages
1,064
That is close! :biggrin: Is there any way that the blank lines can remain?

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

 

dbmathis

Well-known Member
Joined
Sep 22, 2002
Messages
1,064
No formulas in K11:M41. There are formulas in cells all over the rest of the sheet.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,130
Messages
5,576,270
Members
412,710
Latest member
Maged elmasry
Top