Deletion of rows in a column according to a given criteria
 

Hi everyone,

Hope you can help me with this. I've written a macro that searches every
cell in a given column for an empty cell (""), and deletes the row of that
cell. However, given that the sheets that will be used don't have equal
number of observations, the macro I've written checks every single until row
65536, even though, the cells might be empty because the the database has
reached its ends. The result is a slow macro.
Here goes the code:
Sub main_varcodes_correction()

cnt = 1
cnt1 = 0
hlp = 0
zero = 0
For cnt = 1 To 65536

cnt1 = cnt1 + 1

If Cells(cnt1, 7) = "" Then
Rows(cnt1).Select
Selection.Delete Shift:=xlUp
cnt1 = cnt1 - 1
hlp = hlp + 1
Else
hlp = 0
End If


If hlp = 100 Then
r = (cnt1 + 1) & ":" & "65536"
Rows(r).Select
Selection.Delete Shift:=xlUp
cnt = 65536
End If

Next cnt

End Sub

Does anyone know a way to improve the macro. it takes almost 3minutes to
process one file, and I have 200 plus to process...

Thanks,

Nuno

Deletion of rows in a column according to a given criteria
 

The following will work on column A where less than 50000 rows are used, the
trick is in testing the row number of Activecell.end(xldown)


Range("A1").Select
Do Until ActiveCell.End(xlDown).Row 50000
If ActiveCell = "" Then
Rows(ActiveCell.Row).Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Loop




"Nuno" wrote in message
...
Hi everyone,

Hope you can help me with this. I've written a macro that searches every
cell in a given column for an empty cell (""), and deletes the row of that
cell. However, given that the sheets that will be used don't have equal
number of observations, the macro I've written checks every single until
row
65536, even though, the cells might be empty because the the database has
reached its ends. The result is a slow macro.
Here goes the code:
Sub main_varcodes_correction()

cnt = 1
cnt1 = 0
hlp = 0
zero = 0
For cnt = 1 To 65536

cnt1 = cnt1 + 1

If Cells(cnt1, 7) = "" Then
Rows(cnt1).Select
Selection.Delete Shift:=xlUp
cnt1 = cnt1 - 1
hlp = hlp + 1
Else
hlp = 0
End If


If hlp = 100 Then
r = (cnt1 + 1) & ":" & "65536"
Rows(r).Select
Selection.Delete Shift:=xlUp
cnt = 65536
End If

Next cnt

End Sub

Does anyone know a way to improve the macro. it takes almost 3minutes to
process one file, and I have 200 plus to process...

Thanks,

Nuno

Deletion of rows in a column according to a given criteria
 

and obviously it will work uch quicker with screenupdating turned off, and
quicker still if you go down the column using offset(i,0) rather than
literally selecting the cell!

"Stuart" wrote in message
...
The following will work on column A where less than 50000 rows are used,
the trick is in testing the row number of Activecell.end(xldown)


Range("A1").Select
Do Until ActiveCell.End(xlDown).Row 50000
If ActiveCell = "" Then
Rows(ActiveCell.Row).Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Loop




"Nuno" wrote in message
...
Hi everyone,

Hope you can help me with this. I've written a macro that searches every
cell in a given column for an empty cell (""), and deletes the row of
that
cell. However, given that the sheets that will be used don't have equal
number of observations, the macro I've written checks every single until
row
65536, even though, the cells might be empty because the the database has
reached its ends. The result is a slow macro.
Here goes the code:
Sub main_varcodes_correction()

cnt = 1
cnt1 = 0
hlp = 0
zero = 0
For cnt = 1 To 65536

cnt1 = cnt1 + 1

If Cells(cnt1, 7) = "" Then
Rows(cnt1).Select
Selection.Delete Shift:=xlUp
cnt1 = cnt1 - 1
hlp = hlp + 1
Else
hlp = 0
End If


If hlp = 100 Then
r = (cnt1 + 1) & ":" & "65536"
Rows(r).Select
Selection.Delete Shift:=xlUp
cnt = 65536
End If

Next cnt

End Sub

Does anyone know a way to improve the macro. it takes almost 3minutes to
process one file, and I have 200 plus to process...

Thanks,

Nuno

Deletion of rows in a column according to a given criteria
 

Instead of
For cnt = 1 To 65536
Try
For cnt = 1 To Activesheet.UsedRange.Rows.Count

This will only go through the rows to the last one with data in it.