Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iteratively delete rows from a spreadsheet
I have a very large spreadsheet (about 50,000 rows, and to CM of
columns) with blocks of data 20 rows high (rows 1-20 are from Building A, rows 21-40 are from Building B, etc). However, not every row in each block of 20 has information in it - some are just placeholders. For example, some blocks may have rows 1-18 filled with data while other blocks may have only 1-6 filled with data. I am interested in programming a macro that would delete the placeholder rows out of the spreadsheet based on a certain criteria. This would probably halve the size of my spreadsheet. In faux-code: For row i i = 1 to 50,000 If Column B = #NA Delete row i Is this something that can be done with a macro, or do I need to go through all of these rows by hand? Obviously, I haven't programmed macros before (I've taken code and run it), but I've programmed in other languages (Java, C#). Could someone point me in the right direction? Thanks, V. Hagberg |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iteratively delete rows from a spreadsheet
If you no longer want to maintain the 20 row grouping per building, then it
is better to work from the bottom up when deleteing rows. This will avoid the inadvertant skipping of consecutive rows that meet the delete criteria because of the default shift up. Sub delRw() Dim lr As Long lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row For i = lr To 2 If Cells(i, 2) = #NA Then Row(i).Delete End If Next End Sub "Vicki Hagberg" wrote in message ... I have a very large spreadsheet (about 50,000 rows, and to CM of columns) with blocks of data 20 rows high (rows 1-20 are from Building A, rows 21-40 are from Building B, etc). However, not every row in each block of 20 has information in it - some are just placeholders. For example, some blocks may have rows 1-18 filled with data while other blocks may have only 1-6 filled with data. I am interested in programming a macro that would delete the placeholder rows out of the spreadsheet based on a certain criteria. This would probably halve the size of my spreadsheet. In faux-code: For row i i = 1 to 50,000 If Column B = #NA Delete row i Is this something that can be done with a macro, or do I need to go through all of these rows by hand? Obviously, I haven't programmed macros before (I've taken code and run it), but I've programmed in other languages (Java, C#). Could someone point me in the right direction? Thanks, V. Hagberg |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iteratively delete rows from a spreadsheet
Had a senior moment there.
Change the line: For i = lr To 2 To: For i = lr To 2 Step -1 "Vicki Hagberg" wrote in message ... I have a very large spreadsheet (about 50,000 rows, and to CM of columns) with blocks of data 20 rows high (rows 1-20 are from Building A, rows 21-40 are from Building B, etc). However, not every row in each block of 20 has information in it - some are just placeholders. For example, some blocks may have rows 1-18 filled with data while other blocks may have only 1-6 filled with data. I am interested in programming a macro that would delete the placeholder rows out of the spreadsheet based on a certain criteria. This would probably halve the size of my spreadsheet. In faux-code: For row i i = 1 to 50,000 If Column B = #NA Delete row i Is this something that can be done with a macro, or do I need to go through all of these rows by hand? Obviously, I haven't programmed macros before (I've taken code and run it), but I've programmed in other languages (Java, C#). Could someone point me in the right direction? Thanks, V. Hagberg |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iteratively delete rows from a spreadsheet
Option Explicit
Sub Testme01() Dim iRow as long Dim LastRow as long dim FirstRow as long with worksheets("Somesheetnamehere") FirstRow = 1 lastrow = .cells(.rows.count,"B").end(xlup).row for irow = lastrow to firstrow step -1 if iserror(.cells(irow,"B").value) then .rows(irow).delete end if next irow end with End Sub This actually tested for any error in column B. You may want to use: if lcase(.cells(irow,"B").text) = lcase("#N/A") then for just #n/a's. Vicki Hagberg wrote: I have a very large spreadsheet (about 50,000 rows, and to CM of columns) with blocks of data 20 rows high (rows 1-20 are from Building A, rows 21-40 are from Building B, etc). However, not every row in each block of 20 has information in it - some are just placeholders. For example, some blocks may have rows 1-18 filled with data while other blocks may have only 1-6 filled with data. I am interested in programming a macro that would delete the placeholder rows out of the spreadsheet based on a certain criteria. This would probably halve the size of my spreadsheet. In faux-code: For row i i = 1 to 50,000 If Column B = #NA Delete row i Is this something that can be done with a macro, or do I need to go through all of these rows by hand? Obviously, I haven't programmed macros before (I've taken code and run it), but I've programmed in other languages (Java, C#). Could someone point me in the right direction? Thanks, V. Hagberg -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fast Way to Delete Rows from Big Spreadsheet (Using VBA) | Excel Programming | |||
How do i delete empty rows in a spreadsheet | Excel Discussion (Misc queries) | |||
Macro to delete certain rows in a spreadsheet | Excel Programming | |||
Macro/VBA to delete rows in Spreadsheet | Excel Programming | |||
delete all blank rows in a spreadsheet | Excel Programming |