Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can someone help me?
I have an Excel spreadsheet with 10 columns. If columns 5 through 10 are
empty, I want to delete the entire row and move on to the end of my spreadsheet. Is there a function or an easy Visual Basic macro I can run to accomplish this task? Thanks in advance for your help... I do not know what I'd do without this resource! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can someone help me?
Press Alt + F11 and paste this into the code window. If the code window is
dark, then on the menu bar of the VBE, select InsertModule. To run the macro, in Excel select ToolsMacroMacros click on the macro name then Run. Sub delRws() Dim lr As Long, sh As Worksheet Set sh = ActiveSheet lr = sh.Cells(Rows.Count, 1).End(xlUp).Row For i = lr To 2 Step -1 If WorksheetFunction.CountA(Range(sh.Cells(i, 5), _ sh.Cells(i, 10))) = 0 Then Rows(i).Delete End If Next sh.Range("A2").End(xlDown).Select End Sub "ILoveMyCorgi" wrote in message ... I have an Excel spreadsheet with 10 columns. If columns 5 through 10 are empty, I want to delete the entire row and move on to the end of my spreadsheet. Is there a function or an easy Visual Basic macro I can run to accomplish this task? Thanks in advance for your help... I do not know what I'd do without this resource! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can someone help me?
I am confused at what you ultimately want done here given the wording of
your message; specifically, this part... "I want to delete the entire row and move on to the end of my spreadsheet." Does that mean you are only examining one row and if the condition is met for that one row, delete it and go to the end of your data? If so, which row are we talking about... the row with the active cell or some fixed row which you neglected to tell us? And where at the end of your date... which column? -- Rick (MVP - Excel) "ILoveMyCorgi" wrote in message ... I have an Excel spreadsheet with 10 columns. If columns 5 through 10 are empty, I want to delete the entire row and move on to the end of my spreadsheet. Is there a function or an easy Visual Basic macro I can run to accomplish this task? Thanks in advance for your help... I do not know what I'd do without this resource! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can someone help me?
You don't need a macro.
In column 11 enter =IF(COUNTA(E1:J1)=0,"XX","YY") Copy down and autofilter for XX then delete the rows. Macro.............. Sub DeleteRows_If_E_to_J_MT() Dim lRow As Long Dim StartRow As Long Dim EndRow As Long With ActiveSheet StartRow = 1 EndRow = 1000 'adjust to suit For lRow = EndRow To StartRow Step -1 If Application.CountA(.Range(.Cells(lRow, "E"), _ .Cells(lRow, "J"))) = 0 Then .Rows(lRow).Delete Next End With End Sub Gord Dibben MS Excel MVP On Thu, 1 Apr 2010 11:13:02 -0700, ILoveMyCorgi wrote: I have an Excel spreadsheet with 10 columns. If columns 5 through 10 are empty, I want to delete the entire row and move on to the end of my spreadsheet. Is there a function or an easy Visual Basic macro I can run to accomplish this task? Thanks in advance for your help... I do not know what I'd do without this resource! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can someone help me?
Hi,
How about this Sub Delete_Rows() Set sht = Sheets("Sheet1")'Change to suit lastrow = sht.Cells(Cells.Rows.Count, "A").End(xlUp).Row Set MyRange = sht.Range("A1:A" & lastrow) For Each c In MyRange If WorksheetFunction.CountA(c.Offset(, 5).Resize(, 5)) = 0 Then c.EntireRow.Delete End If Next End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "ILoveMyCorgi" wrote: I have an Excel spreadsheet with 10 columns. If columns 5 through 10 are empty, I want to delete the entire row and move on to the end of my spreadsheet. Is there a function or an easy Visual Basic macro I can run to accomplish this task? Thanks in advance for your help... I do not know what I'd do without this resource! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can someone help me?
OOPS,
That would miss 2 consecutive rows, try this instead Sub Delete_Rows() Dim CopyRange As Range Set sht = Sheets("Sheet1") lastrow = sht.Cells(Cells.Rows.Count, "A").End(xlUp).Row Set MyRange = sht.Range("A1:A" & lastrow) For Each c In MyRange If WorksheetFunction.CountA(c.Offset(, 5).Resize(, 5)) = 0 Then If CopyRange Is Nothing Then Set CopyRange = c.EntireRow Else Set CopyRange = Union(CopyRange, c.EntireRow) End If End If Next If Not CopyRange Is Nothing Then CopyRange.Delete End If End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "ILoveMyCorgi" wrote: I have an Excel spreadsheet with 10 columns. If columns 5 through 10 are empty, I want to delete the entire row and move on to the end of my spreadsheet. Is there a function or an easy Visual Basic macro I can run to accomplish this task? Thanks in advance for your help... I do not know what I'd do without this resource! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can someone help me?
I am sorry. What I am trying to do is analyze each row and if columns E
through I are empty, delete that particular row, go on to the next row and analyze, etc. all the way to the end of my populated rows. One thing I also forgot to mention is that I have three worksheets, Sheet1, Sheet2, Sheet3 with data where I need to delete the rows without data in the last five columns. I hope I make sense. thanks for your time. "Rick Rothstein" wrote: I am confused at what you ultimately want done here given the wording of your message; specifically, this part... "I want to delete the entire row and move on to the end of my spreadsheet." Does that mean you are only examining one row and if the condition is met for that one row, delete it and go to the end of your data? If so, which row are we talking about... the row with the active cell or some fixed row which you neglected to tell us? And where at the end of your date... which column? -- Rick (MVP - Excel) "ILoveMyCorgi" wrote in message ... I have an Excel spreadsheet with 10 columns. If columns 5 through 10 are empty, I want to delete the entire row and move on to the end of my spreadsheet. Is there a function or an easy Visual Basic macro I can run to accomplish this task? Thanks in advance for your help... I do not know what I'd do without this resource! . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can someone help me?
Thank you... it did not work. I still had rows with data in columns A
through D and no data in columns E through J are empty yet the rows have not been deleted. "Mike H" wrote: Hi, How about this Sub Delete_Rows() Set sht = Sheets("Sheet1")'Change to suit lastrow = sht.Cells(Cells.Rows.Count, "A").End(xlUp).Row Set MyRange = sht.Range("A1:A" & lastrow) For Each c In MyRange If WorksheetFunction.CountA(c.Offset(, 5).Resize(, 5)) = 0 Then c.EntireRow.Delete End If Next End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "ILoveMyCorgi" wrote: I have an Excel spreadsheet with 10 columns. If columns 5 through 10 are empty, I want to delete the entire row and move on to the end of my spreadsheet. Is there a function or an easy Visual Basic macro I can run to accomplish this task? Thanks in advance for your help... I do not know what I'd do without this resource! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can someone help me?
Thank you... it did not work. I still had rows with data in columns A
through D and no data in columns E through J are empty yet the rows have not been deleted. "JLGWhiz" wrote: Press Alt + F11 and paste this into the code window. If the code window is dark, then on the menu bar of the VBE, select InsertModule. To run the macro, in Excel select ToolsMacroMacros click on the macro name then Run. Sub delRws() Dim lr As Long, sh As Worksheet Set sh = ActiveSheet lr = sh.Cells(Rows.Count, 1).End(xlUp).Row For i = lr To 2 Step -1 If WorksheetFunction.CountA(Range(sh.Cells(i, 5), _ sh.Cells(i, 10))) = 0 Then Rows(i).Delete End If Next sh.Range("A2").End(xlDown).Select End Sub "ILoveMyCorgi" wrote in message ... I have an Excel spreadsheet with 10 columns. If columns 5 through 10 are empty, I want to delete the entire row and move on to the end of my spreadsheet. Is there a function or an easy Visual Basic macro I can run to accomplish this task? Thanks in advance for your help... I do not know what I'd do without this resource! . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can someone help me?
I liked the formula but only yielded YY and no XX even though there were
columns without data from E through J. I also tried the macro it did not work. I still had rows with data in columns A through D and no data in columns E through J are empty yet the rows have not been deleted. "Gord Dibben" wrote: You don't need a macro. In column 11 enter =IF(COUNTA(E1:J1)=0,"XX","YY") Copy down and autofilter for XX then delete the rows. Macro.............. Sub DeleteRows_If_E_to_J_MT() Dim lRow As Long Dim StartRow As Long Dim EndRow As Long With ActiveSheet StartRow = 1 EndRow = 1000 'adjust to suit For lRow = EndRow To StartRow Step -1 If Application.CountA(.Range(.Cells(lRow, "E"), _ .Cells(lRow, "J"))) = 0 Then .Rows(lRow).Delete Next End With End Sub Gord Dibben MS Excel MVP On Thu, 1 Apr 2010 11:13:02 -0700, ILoveMyCorgi wrote: I have an Excel spreadsheet with 10 columns. If columns 5 through 10 are empty, I want to delete the entire row and move on to the end of my spreadsheet. Is there a function or an easy Visual Basic macro I can run to accomplish this task? Thanks in advance for your help... I do not know what I'd do without this resource! . |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can someone help me?
Did you read any of the other replies you got?
Do you have just the three sheets or more but only need deleting on Sheets 1 through 3? I will assume three only and column A will be used to determine end of data for each sheet. Sub DeleteRows_If_E_to_J_MT() Dim lRow As Long Dim StartRow As Long Dim EndRow As Long Dim ws As Worksheet EndRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp) _ .Offset(1, 0).Row For Each ws In ActiveWorkbook.Worksheets With ws StartRow = 1 For lRow = EndRow To StartRow Step -1 If Application.CountA(.Range(.Cells(lRow, "E"), _ .Cells(lRow, "J"))) = 0 Then .Rows(lRow).Delete Next End With Next End Sub Gord Dibben MS Excel MVP On Thu, 1 Apr 2010 15:17:07 -0700, ILoveMyCorgi wrote: I am sorry. What I am trying to do is analyze each row and if columns E through I are empty, delete that particular row, go on to the next row and analyze, etc. all the way to the end of my populated rows. One thing I also forgot to mention is that I have three worksheets, Sheet1, Sheet2, Sheet3 with data where I need to delete the rows without data in the last five columns. I hope I make sense. thanks for your time. "Rick Rothstein" wrote: I am confused at what you ultimately want done here given the wording of your message; specifically, this part... "I want to delete the entire row and move on to the end of my spreadsheet." Does that mean you are only examining one row and if the condition is met for that one row, delete it and go to the end of your data? If so, which row are we talking about... the row with the active cell or some fixed row which you neglected to tell us? And where at the end of your date... which column? -- Rick (MVP - Excel) "ILoveMyCorgi" wrote in message ... I have an Excel spreadsheet with 10 columns. If columns 5 through 10 are empty, I want to delete the entire row and move on to the end of my spreadsheet. Is there a function or an easy Visual Basic macro I can run to accomplish this task? Thanks in advance for your help... I do not know what I'd do without this resource! . |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can someone help me?
I'm starting to think that your cells in E through J are not really empty.
Do you have formulas returning blanks or spaces in these cells? Gord On Thu, 1 Apr 2010 15:55:02 -0700, ILoveMyCorgi wrote: I liked the formula but only yielded YY and no XX even though there were columns without data from E through J. I also tried the macro it did not work. I still had rows with data in columns A through D and no data in columns E through J are empty yet the rows have not been deleted. "Gord Dibben" wrote: You don't need a macro. In column 11 enter =IF(COUNTA(E1:J1)=0,"XX","YY") Copy down and autofilter for XX then delete the rows. Macro.............. Sub DeleteRows_If_E_to_J_MT() Dim lRow As Long Dim StartRow As Long Dim EndRow As Long With ActiveSheet StartRow = 1 EndRow = 1000 'adjust to suit For lRow = EndRow To StartRow Step -1 If Application.CountA(.Range(.Cells(lRow, "E"), _ .Cells(lRow, "J"))) = 0 Then .Rows(lRow).Delete Next End With End Sub Gord Dibben MS Excel MVP On Thu, 1 Apr 2010 11:13:02 -0700, ILoveMyCorgi wrote: I have an Excel spreadsheet with 10 columns. If columns 5 through 10 are empty, I want to delete the entire row and move on to the end of my spreadsheet. Is there a function or an easy Visual Basic macro I can run to accomplish this task? Thanks in advance for your help... I do not know what I'd do without this resource! . |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can someone help me?
Spoke too soon. I now see your replies to other posting.
Gord On Thu, 01 Apr 2010 16:06:18 -0700, Gord Dibben <gorddibbATshawDOTca wrote: Did you read any of the other replies you got? Do you have just the three sheets or more but only need deleting on Sheets 1 through 3? I will assume three only and column A will be used to determine end of data for each sheet. Sub DeleteRows_If_E_to_J_MT() Dim lRow As Long Dim StartRow As Long Dim EndRow As Long Dim ws As Worksheet EndRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp) _ .Offset(1, 0).Row For Each ws In ActiveWorkbook.Worksheets With ws StartRow = 1 For lRow = EndRow To StartRow Step -1 If Application.CountA(.Range(.Cells(lRow, "E"), _ .Cells(lRow, "J"))) = 0 Then .Rows(lRow).Delete Next End With Next End Sub Gord Dibben MS Excel MVP On Thu, 1 Apr 2010 15:17:07 -0700, ILoveMyCorgi wrote: I am sorry. What I am trying to do is analyze each row and if columns E through I are empty, delete that particular row, go on to the next row and analyze, etc. all the way to the end of my populated rows. One thing I also forgot to mention is that I have three worksheets, Sheet1, Sheet2, Sheet3 with data where I need to delete the rows without data in the last five columns. I hope I make sense. thanks for your time. "Rick Rothstein" wrote: I am confused at what you ultimately want done here given the wording of your message; specifically, this part... "I want to delete the entire row and move on to the end of my spreadsheet." Does that mean you are only examining one row and if the condition is met for that one row, delete it and go to the end of your data? If so, which row are we talking about... the row with the active cell or some fixed row which you neglected to tell us? And where at the end of your date... which column? -- Rick (MVP - Excel) "ILoveMyCorgi" wrote in message ... I have an Excel spreadsheet with 10 columns. If columns 5 through 10 are empty, I want to delete the entire row and move on to the end of my spreadsheet. Is there a function or an easy Visual Basic macro I can run to accomplish this task? Thanks in advance for your help... I do not know what I'd do without this resource! . |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can someone help me?
haven't really tested it, but see if this will work, if the cells are
actually blank Sub Macro3() Dim ws As Worksheet Dim i As Long Dim lastrow As Long For i = 1 To 3 Set ws = Worksheets(i) lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row With ws.Range("A1:J" & lastrow) .AutoFilter Field:=5, Criteria1:="=" .AutoFilter Field:=6, Criteria1:="=" .AutoFilter Field:=7, Criteria1:="=" .AutoFilter Field:=8, Criteria1:="=" .AutoFilter Field:=9, Criteria1:="=" .AutoFilter Field:=10, Criteria1:="=" End With ws.Range("A2:J" & lastrow).SpecialCells(xlCellTypeVisible).EntireRow .Delete ws.AutoFilterMode = False Next End Sub -- Gary Keramidas Excel 2003 "ILoveMyCorgi" wrote in message ... I have an Excel spreadsheet with 10 columns. If columns 5 through 10 are empty, I want to delete the entire row and move on to the end of my spreadsheet. Is there a function or an easy Visual Basic macro I can run to accomplish this task? Thanks in advance for your help... I do not know what I'd do without this resource! |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can someone help me?
I think you are right, Gord. The code I suggested worked just fine when
tested. The OP has to have some cells with "invisible" data in them for the code not to work. Or, probably formulas with a "" value. "Gord Dibben" <gorddibbATshawDOTca wrote in message ... I'm starting to think that your cells in E through J are not really empty. Do you have formulas returning blanks or spaces in these cells? Gord On Thu, 1 Apr 2010 15:55:02 -0700, ILoveMyCorgi wrote: I liked the formula but only yielded YY and no XX even though there were columns without data from E through J. I also tried the macro it did not work. I still had rows with data in columns A through D and no data in columns E through J are empty yet the rows have not been deleted. "Gord Dibben" wrote: You don't need a macro. In column 11 enter =IF(COUNTA(E1:J1)=0,"XX","YY") Copy down and autofilter for XX then delete the rows. Macro.............. Sub DeleteRows_If_E_to_J_MT() Dim lRow As Long Dim StartRow As Long Dim EndRow As Long With ActiveSheet StartRow = 1 EndRow = 1000 'adjust to suit For lRow = EndRow To StartRow Step -1 If Application.CountA(.Range(.Cells(lRow, "E"), _ .Cells(lRow, "J"))) = 0 Then .Rows(lRow).Delete Next End With End Sub Gord Dibben MS Excel MVP On Thu, 1 Apr 2010 11:13:02 -0700, ILoveMyCorgi wrote: I have an Excel spreadsheet with 10 columns. If columns 5 through 10 are empty, I want to delete the entire row and move on to the end of my spreadsheet. Is there a function or an easy Visual Basic macro I can run to accomplish this task? Thanks in advance for your help... I do not know what I'd do without this resource! . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|