Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows in which the result of the formula is " "
This is my formula in each row in my table has an if/then statement to pick
up corresponding values from the same rows in other worksheets if the value in the subsequent worksheet is greater than 0, otherwise leave blank. Now I want to be able to delete the rows with a "" value. I have tried to write a Do/Until loop to look at each row in the table, but I cannot figure out how to word the statement telling it what to do when it encounters the "" values. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows in which the result of the formula is " "
I would recommend using a For...Each Loop. The For...Each is more efficient
than a Do...Loop when you are working with objects. This procedure below will scan down Col. A looking for values = "", if it finds one the entire row is deleted. Sub DeleteRows() Dim LastRow As Long Dim myRange As Range Dim cell As Range ' finds last row in Col.A LastRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row ' range of cells to test values Set myRange = Sheets("Sheet1").Range(Cells(2, 1), Cells(LastRow, 1)) ' loop thru each cell in myRange to find "" values For Each cell In myRange If cell.Value = "" Then cell.EntireRow.Delete End If Next cell End Sub Hope this helps. If so please click "yes" below. -- Cheers, Ryan "marcia2026" wrote: This is my formula in each row in my table has an if/then statement to pick up corresponding values from the same rows in other worksheets if the value in the subsequent worksheet is greater than 0, otherwise leave blank. Now I want to be able to delete the rows with a "" value. I have tried to write a Do/Until loop to look at each row in the table, but I cannot figure out how to word the statement telling it what to do when it encounters the "" values. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows in which the result of the formula is " "
Sub remove_rows()
cl = "A" n = Cells(Rows.Count, cl).End(xlUp).Row For i = n To 1 Step -1 If Cells(i, cl).Value = "" Then Cells(i, cl).EntireRow.Delete End If Next End Sub looks for blanks in column A. Adjust to suit your needs. -- Gary''s Student - gsnu200795 "marcia2026" wrote: This is my formula in each row in my table has an if/then statement to pick up corresponding values from the same rows in other worksheets if the value in the subsequent worksheet is greater than 0, otherwise leave blank. Now I want to be able to delete the rows with a "" value. I have tried to write a Do/Until loop to look at each row in the table, but I cannot figure out how to word the statement telling it what to do when it encounters the "" values. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows in which the result of the formula is " "
Hi Ryan, I copied your code into my worksheet, when I tried to run it I
received the message "subscript out of range" thanks, marcia "RyanH" wrote: I would recommend using a For...Each Loop. The For...Each is more efficient than a Do...Loop when you are working with objects. This procedure below will scan down Col. A looking for values = "", if it finds one the entire row is deleted. Sub DeleteRows() Dim LastRow As Long Dim myRange As Range Dim cell As Range ' finds last row in Col.A LastRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row ' range of cells to test values Set myRange = Sheets("Sheet1").Range(Cells(2, 1), Cells(LastRow, 1)) ' loop thru each cell in myRange to find "" values For Each cell In myRange If cell.Value = "" Then cell.EntireRow.Delete End If Next cell End Sub Hope this helps. If so please click "yes" below. -- Cheers, Ryan "marcia2026" wrote: This is my formula in each row in my table has an if/then statement to pick up corresponding values from the same rows in other worksheets if the value in the subsequent worksheet is greater than 0, otherwise leave blank. Now I want to be able to delete the rows with a "" value. I have tried to write a Do/Until loop to look at each row in the table, but I cannot figure out how to word the statement telling it what to do when it encounters the "" values. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows in which the result of the formula is " "
worked. Thanks
"Gary''s Student" wrote: Sub remove_rows() cl = "A" n = Cells(Rows.Count, cl).End(xlUp).Row For i = n To 1 Step -1 If Cells(i, cl).Value = "" Then Cells(i, cl).EntireRow.Delete End If Next End Sub looks for blanks in column A. Adjust to suit your needs. -- Gary''s Student - gsnu200795 "marcia2026" wrote: This is my formula in each row in my table has an if/then statement to pick up corresponding values from the same rows in other worksheets if the value in the subsequent worksheet is greater than 0, otherwise leave blank. Now I want to be able to delete the rows with a "" value. I have tried to write a Do/Until loop to look at each row in the table, but I cannot figure out how to word the statement telling it what to do when it encounters the "" values. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows in which the result of the formula is " "
Sorry. I spoke too soon. Actually, this one didn't work either.
"marcia2026" wrote: worked. Thanks "Gary''s Student" wrote: Sub remove_rows() cl = "A" n = Cells(Rows.Count, cl).End(xlUp).Row For i = n To 1 Step -1 If Cells(i, cl).Value = "" Then Cells(i, cl).EntireRow.Delete End If Next End Sub looks for blanks in column A. Adjust to suit your needs. -- Gary''s Student - gsnu200795 "marcia2026" wrote: This is my formula in each row in my table has an if/then statement to pick up corresponding values from the same rows in other worksheets if the value in the subsequent worksheet is greater than 0, otherwise leave blank. Now I want to be able to delete the rows with a "" value. I have tried to write a Do/Until loop to look at each row in the table, but I cannot figure out how to word the statement telling it what to do when it encounters the "" values. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows in which the result of the formula is " "
ryan -
for-next or do-loop won't work in deleting rows moving from the top to the bottom.......... if you are on row 19 & delete the entire row, row 20 moves up into row 19, but the macro goes to row 20. so you've missed a row. that's why gary went from the bottom up to the top - that way when you delete the row you don't miss one. :) susan On Jul 15, 12:50*pm, RyanH wrote: I would recommend using a For...Each Loop. *The For...Each is more efficient than a Do...Loop when you are working with objects. *This procedure below will scan down Col. A looking for values = "", if it finds one the entire row is deleted. Sub DeleteRows() Dim LastRow As Long Dim myRange As Range Dim cell As Range * * ' finds last row in Col.A * * LastRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row * * ' range of cells to test values * * Set myRange = Sheets("Sheet1").Range(Cells(2, 1), Cells(LastRow, 1)) * * ' loop thru each cell in myRange to find "" values * * For Each cell In myRange * * * * If cell.Value = "" Then * * * * * * cell.EntireRow.Delete * * * * End If * * Next cell End Sub Hope this helps. *If so please click "yes" below. -- Cheers, Ryan "marcia2026" wrote: This is my formula in each row in my table has an if/then statement to pick up corresponding values from the same rows in other worksheets if the value in the subsequent worksheet is greater than 0, otherwise leave blank. *Now I want to be able to delete the rows with a "" value. *I have tried to write a Do/Until loop to look at each row in the table, but I cannot figure out how to word the statement telling it what to do when it encounters the "" values.- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows in which the result of the formula is " "
this is the same (basically) as gary's, but the variables are
declared.......... =========================== Option Explicit Sub step_backwards() Dim myLastRow As Long Dim r As Long Dim c As Range myLastRow = ActiveSheet.Cells(10000, 1).End(xlUp).Row For r = myLastRow To 1 Step -1 Set c = ActiveSheet.Range("a" & r) If c.Value = "" Then c.EntireRow.Delete End If Next r End Sub ========================= again, it looks for blanks in column A. hope it helps susan On Jul 15, 2:08*pm, marcia2026 wrote: Sorry. *I spoke too soon. *Actually, this one didn't work either. "marcia2026" wrote: worked. *Thanks "Gary''s Student" wrote: Sub remove_rows() cl = "A" n = Cells(Rows.Count, cl).End(xlUp).Row For i = n To 1 Step -1 * * If Cells(i, cl).Value = "" Then * * * * Cells(i, cl).EntireRow.Delete * * End If Next End Sub looks for blanks in column A. *Adjust to suit your needs. -- Gary''s Student - gsnu200795 "marcia2026" wrote: This is my formula in each row in my table has an if/then statement to pick up corresponding values from the same rows in other worksheets if the value in the subsequent worksheet is greater than 0, otherwise leave blank. *Now I want to be able to delete the rows with a "" value. *I have tried to write a Do/Until loop to look at each row in the table, but I cannot figure out how to word the statement telling it what to do when it encounters the "" values.- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows in which the result of the formula is " "
Skip the loops altogether:
This looks in column a for mts and deletes the rows when found: Sub RemoveMTCellRows() On Error Resume Next ActiveSheet.Columns(1).SpecialCells(xlCellTypeBlan ks).EntireRow.Delete On Error GoTo 0 End Sub Cliff Edwards |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Result is "V6", need Excel to use cell "V6", not the resul | Excel Worksheet Functions | |||
delete rows if cell in row contains "a" or "o" or empty | Excel Programming | |||
Look for cell containing "Initial" then if the next cell after equals "Final" then delete both rows. | Excel Programming | |||
If changed array formula reduce ""\""\""\ - signs to #Missing, will it make ... | Excel Programming | |||
Search "Total" in all worksheets and delete rows containing "Total" | Excel Programming |