Loop ? again

[chrisnsmith]

I posed my problem earlier and Dave Peterson gave me the solution. However
what I said I needed was incorrect. I have only shown 11 rows of what is
actually 50 rows per section (columns A,B & C---columns G,H & I). This
worksheet is set up
this way for printing purposes only. Columns G,H &I should be considered as
extentions of columns A,B & C. What I want to do is, for each row in either
section
that has a value of < 1, I want to clear the contents of that row and move
the other rows up to fill the empty row. This means that every row must
shift upwards from
row 50 of columns G,H & I, putting the contents of the top rows of columns
G,H & I
at the bottom of columns A,B & C and the blank rows at the bottom of columns
G,H & I.

I would also like to sort the entries by oldest date first.

Help.

A B C G H
I

Account Date Amount Account Date Amount
14 cd 2/13/2009 4 bf 2/13/2009 6
15 wf 2/13/2009 3 af 2/13/2009 2
16 we 2/13/2009 56 gf 2/13/2009 34
17 qe 2/13/2009 23 bg 2/13/2009 16
18 rt 2/13/2009 0 fg 2/13/2009 0
19 yu 2/13/2009 23 yk 2/13/2009 45
20 ty 2/13/2009 8 lu 2/13/2009 2
21 ui 2/13/2009 9 tq 2/13/2009 8
22 op 2/13/2009 0 de 2/13/2009 0
23 th 2/13/2009 34 wc 2/13/2009 11
24 bn 2/13/2009 2 bz 2/13/2009 98
25 cv 2/13/2009 1 hm 2/13/2009 1

 

[Don Guillett]

Apparently you did not see my solution. And, you should stay in the original
thread.
I have further modified to do the copy/sort/delte

Sub cleariflessthanzeroOffsetLoopcols()
Dim mc, i As Long
For Each mc In Array(3, 9)
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
If Cells(i, mc) < 1 Then Cells(i, mc - 2).Resize(, 3).Delete Shift:=xlUp
Next i
Next mc

'copyG_sortdatabydateC
Dim dlr, slr As Long
slr = Cells(Rows.Count, "a").End(xlUp).Row
dlr = Cells(Rows.Count, "a").End(xlUp).Row + 1

Range("g2:i" & slr).Copy Cells(dlr, "a")
slr = Cells(Rows.Count, "a").End(xlUp).Row

Range("a2:c" & slr).Sort key1:=Range("b2")

Columns("g:i").Delete

End Sub

 

[Sheeloo]

Warning - do test on a copy of your data...
Also keep a copy till you are sure this works as you want.

Use the macro below;
(it does not do sorting... we can add that if you want)

Assumption: There is nothing else on the sheet
Sub test()
With ActiveSheet
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
..Range("D2:F" & lastrow).Cut Destination:=.Range("A" & (lastrow + 1))
srow = lastrow + 1
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = lastrow To 2 Step -1
If .Cells(i, 3) < 1 Then
Cells(i, 1).EntireRow.Delete
End If
Next

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
..Range("A" & srow & ":C" & (lastrow + 1)).Cut Destination:=.Range("D2")
End With
End Sub

============================
If you want to evenly distribute the lines then use this
Sub test()
With ActiveSheet
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
..Range("D2:F" & lastrow).Cut Destination:=.Range("A" & (lastrow + 1))

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = lastrow To 2 Step -1
If .Cells(i, 3) < 1 Then
Cells(i, 1).EntireRow.Delete
End If
Next

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
MsgBox lastrow
srow = Int(lastrow / 2) + 2
MsgBox srow

..Range("A" & srow & ":C" & (lastrow + 1)).Cut Destination:=.Range("D2")
End With
End Sub

 

[chrisnsmith]

Don, I tried the codes you have provided and they are not doing what I need.
The code posted by Sheeloo has come the closest to my requirements. Perhaps
I'm not doing a good job of explaining my needs. I changed my worksheet to
include only 40 rows in each section (A,B,C) and (G,H,I). If a row in
columns (A,B,C) is < 1 I need to shift the remaining rows up, which will
leave blank rows at the bottom the
bottom of columns(A,B,C). To fill these empty rows I need to shift whatever
the quantity may be from the top of columns(G,H,I), which will precipitate
the need to shift rows up in (G,H,I) to fill those empty spaces. I hope this
better explains my needs. I'm a baby at Excel and VB, but I have learned
something from all the replies I have received from this discussion group,
whether or not they have suited my needs, because I study how the code is
written for clues as to how it's done.


Thanks for your help

 

[Sheeloo]

Did my code work? Did it do what you wanted? Except of course for the sort.

 

[chrisnsmith]

Sheeloo,
Your code worked only on columns(A,B,C), it did nothing to
columns(G,H,I).
I think perhaps I should add that when I changed my worksheet to only 40
rows, I also removed what I thought was impertinant info at the top of my
worksheet and started my first row at row 3.

Thanks

 

[Sheeloo]

It should have worked on Col D-F also.

Here is the logic;
1. Find the last cell with data in Col A
2. Cut the data in D-F from second row to the last row (say n) with data and
append it below the data in A-C so you have the whole data in Col A-C
3. Delete the rows where Col C < 1
4. Now cut the data from row n+1 till the end and write it in Col D-F from
row 2.

I think this is what you wanted. If still does not work then can you post
the file at wikisend.com or send it to me by mail?

Try the macro again with row 1 as header and data from row 2.

 

[Don Guillett]

What my code did was to delete the a:c part of the row, moving the next row
a:c up. Same thing for g:i. Then move whats left of g:i to the bottom of
a:c>then sort a:c. You are left with a:c sorted(all same date) and no g:i.
If that is not what is desired send your workbook to my address along with a
clear explanation and BEFORE/AFTER examples.
Mine leaves only a:c as shown. Sheeloo's (modified to g:i) does a:c and g:i
differently
a b c a b c
Account Date Amount Account Date Amount
ty 2/13/2009 8 cd 2/13/2009 4
ui 2/13/2009 9 wf 2/13/2009 3
th 2/13/2009 34 we 2/13/2009 56
cd 2/13/2009 4 qe 2/13/2009 23
wf 2/13/2009 3 yu 2/13/2009 23
qe 2/13/2009 23 ty 2/13/2009 8
yu 2/13/2009 23 ui 2/13/2009 9
bn 2/13/2009 2 th 2/13/2009 34
cv 2/13/2009 1 bn 2/13/2009 2
we 2/13/2009 56 cv 2/13/2009 1
lu 2/13/2009 2 bf 2/13/2009 6
gf 2/13/2009 34 af 2/13/2009 2
bg 2/13/2009 16
yk 2/13/2009 45 g h i
bf 2/13/2009 6 Account Date Amount
af 2/13/2009 2 gf 2/13/2009 34
wc 2/13/2009 11 bg 2/13/2009 16
bz 2/13/2009 98 yk 2/13/2009 45
tq 2/13/2009 8 lu 2/13/2009 2
hm 2/13/2009 1 tq 2/13/2009 8
wc 2/13/2009 11
bz 2/13/2009 98
hm 2/13/2009 1

 

[chrisnsmith]

I mad the assumption that you had used columns(G,H,I) in writing your code.
After reading your post I realized you had use columns(D,E,F). I simply
changed your references to column G and everything worked great.
Thanks alot

 

[chrisnsmith]

Need more help.
I had to change my form. My header row now starts at row 6 and my first row
of info now starts in row 7. What do I need to change in your code to make
it work?

 

[chrisnsmith]

Need more help.
I had to change my form. My header row now starts at row 6 and my first row
of info now starts in row 7. What do I need to change in your code to make
it work? Below is a look at my new form.

1.This list must be sent before 3:30 p.m.
2. Firms are responsible for the information Date 2/23/2009
3. supplied and must accept deliveries based
4.on positions shown
5.----------
6.Account Date Amount Account Date Amoont
7.RG 9/3/2008 1 VG 1/29/2009 1
8.DR 9/11/2008 1 KL 1/30/2009 2
9.HP 9/12/2008 PW 2/4/2009 3
and so on to line 30.

 

[chrisnsmith]

Need more help.
I had to change my form. My header row now starts at row 6 and my first row
of info now starts in row 7. What do I need to change in your code to make
it work? Below is a look at my new form.

1.This list must be sent before 3:30 p.m.
2. Firms are responsible for the information Date 2/23/2009
3. supplied and must accept deliveries based
4.on positions shown
5.----------
6.Account Date Amount Account Date Amoont
7.RG 9/3/2008 1 VG 1/29/2009 1
8.DR 9/11/2008 1 KL 1/30/2009 2
9.HP 9/12/2008 PW 2/4/2009 3
and so on to line 30.