need help with loop ? again

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



----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...9-641228e96057&dg=microsoft.public.excel.misc

 

[Roger Govier]

Hi Chris

You need to post the existing code you are using for anyone to help you.
As it stands, we don't know what you are talking about.

 

[chrisnsmith]

Forgot to post the code:

Sub UpdateForms()
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



Need more help.

 

[Roger Govier]

Hi Chris

Try this on a copy of your data as I have not tested it.
I have changed D2 to D7 in all instances, and changed the loop to count back
from the end to row 7, instead of row 2

Sub UpdateForms()
With ActiveSheet
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
..Range("D7:F" & lastrow).Cut Destination:=.Range("A" & (lastrow + 1))
srow = lastrow + 1
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = lastrow To 7 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("D7")
End With
End Sub

 

[chrisnsmith]

Tried it Roger,
It didn't work.

Hi Chris

Try this on a copy of myried it our data as I have not tested it.
I have changed D2 to D7 in all instances, and changed the loop to count back
from the end to row 7, instead of row 2

Sub UpdateForms()
With ActiveSheet
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("D7:F" & lastrow).Cut Destination:=.Range("A" & (lastrow + 1))
srow = lastrow + 1
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = lastrow To 7 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("D7")
End With
End Sub

 

[CurlyDave]

Why did it not work?