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]

You're welcome.

Merry Christmas to you too.

 

[dbmathis]

Thanks everyone on this forum who have helped me over the years. Especially Norie, Jindon, Erik, and Gibbs.

Because of your code that I have studied, I can go back through my posts and solve problems that others have previously provided me solutions for such as this thread.

I just wanted you all to know that not all of us take your code and run. Some of us actually learn from your examples.

This code took me 2 minutes to write. Thanks everyone for teaching us code virgins how it's done.

My solution:

Sub test()
Dim y, z

y = Range("h10:j30"): iii = 1
ReDim z(1 To UBound(y, 1), 1 To UBound(y, 2))
For i = 1 To UBound(y)
    If Not IsEmpty(y(i, 1)) Then
        For ii = 1 To UBound(y, 2)
            z(iii, ii) = y(i, ii)
        Next
        iii = iii + 1
    End If
Next
Range("h10:j30").ClearContents
For i = 1 To UBound(y, 1)
    For ii = 1 To UBound(y, 2)
        Cells(i + 9, ii + 7) = z(i, ii)
    Next
Next

End Sub