Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting rows meeting certain criteria in a particular column | Excel Discussion (Misc queries) | |||
Count NonBlank Rows with Criteria in another column | Excel Worksheet Functions | |||
Sum specific column rows based on 2 different column criteria | Excel Worksheet Functions | |||
Countings rows based on column criteria | Excel Discussion (Misc queries) | |||
Combine deletion Criteria | Excel Programming |