VBA Help

G

Gila

I'm very new to using VBA, other then recording and some basic stuff... I
have a lot to learn. My question is I have built a list of projects (Excel)
in rows with different data for each project in the columns going on for
maybe ten columns. When a project (row) is completed I have a percent
complete in one of the columns. I'd like some help with some code that will
look at a specific column (say H) for anything that is 100% and cut the
entire row which it is in, then past into another sheet on the same workbook.
The other sheet will be a running total of completed projects.

Thank you in advance for any help,
Gila
 
G

Gary''s Student

Perhaps:

Sub lastr()
n = Cells(Rows.Count, "H").End(xlUp).Row
k = 1
For i = n To 1 Step -1
If Cells(i, "H").Value = 1 Then
Cells(i, "H").EntireRow.Copy Sheets("Sheet2").Cells(k, 1)
Cells(i, "H").EntireRow.Delete
k = k + 1
End If
Next
End Sub
 
G

Gila

Thank you Gary''s Student,

This worked great, one thing though.
After the code transferred the row from sheet1 to "sheet2" it placed it into
row one. After testing it a second time I found it to over write the first
transfer in sheet2. Can it be written to transfer the identified row(s)
without overwriting rows with data in sheet2?
 
G

Gila

Another thought...maybe I need it to (insert row(s)) not paste??? And would
prefer to tell it what row to insert. Say row 3, as I have the first few rows
locked under protect sheet.
 
G

Gary''s Student

Sub lastr()
n = Cells(Rows.Count, "H").End(xlUp).Row
Sheets("Sheet2").Activate
k = Application.WorksheetFunction.Max(1, Cells(Rows.Count,
"H").End(xlUp).Row + 1)
Sheets("Sheet1").Activate
For i = n To 1 Step -1
If Cells(i, "H").Value = 1 Then
Cells(i, "H").EntireRow.Copy Sheets("Sheet2").Cells(k, 1)
Cells(i, "H").EntireRow.Delete
k = k + 1
End If
Next
End Sub

will not over-write any existing material on the second sheet.
 
G

Gila

That is great!!!
Based off the code so far I modified it to look for anything with 60 in
column "I" and delete that row. I’d like it to look for anything 60 or higher
(>=60). I don’t know how to do this expression in VBA though.


Sub delete()
n = Cells(Rows.Count, "I").End(xlUp).Row Sheets("Complete").Activate
k = 1
For i = n To 1 Step -1
If Cells(i, "I").Value = 60 Then
Cells(i, "I").EntireRow.delete
k = k + 1
End If
Next
End Sub
 
G

Gord Dibben

Just as it looks...........>=60

Sub delete()
Sheets("Complete").Activate
n = Cells(Rows.Count, "I").End(xlUp).Row
k = 1
For i = n To 1 Step -1
If Cells(i, "I").Value >= 60 Then
Cells(i, "I").EntireRow.delete
k = k + 1
End If
Next
End Sub


Gord Dibben MS Excel MVP
 
G

Gila

thank you... i was missing the space.

Gord Dibben said:
Just as it looks...........>=60

Sub delete()
Sheets("Complete").Activate
n = Cells(Rows.Count, "I").End(xlUp).Row
k = 1
For i = n To 1 Step -1
If Cells(i, "I").Value >= 60 Then
Cells(i, "I").EntireRow.delete
k = k + 1
End If
Next
End Sub


Gord Dibben MS Excel MVP
Click to expand...
 
Top