Can someone help me?
in [Programming]
|
From: Gord Dibben on 1 Apr 2010 19:06 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 <ILoveMyCorgi(a)discussions.microsoft.com> 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" <ILoveMyCorgi(a)discussions.microsoft.com> wrote in message >> news:36203422-F3C3-48FF-A1BE-F6A763561362(a)microsoft.com... >> > 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! >> >> . >>
From: Gord Dibben on 1 Apr 2010 19:12 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 <ILoveMyCorgi(a)discussions.microsoft.com> 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 >> <ILoveMyCorgi(a)discussions.microsoft.com> 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! >> >> . >>
From: Gord Dibben on 1 Apr 2010 19:13 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 ><ILoveMyCorgi(a)discussions.microsoft.com> 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" <ILoveMyCorgi(a)discussions.microsoft.com> wrote in message >>> news:36203422-F3C3-48FF-A1BE-F6A763561362(a)microsoft.com... >>> > 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! >>> >>> . >>>
From: Gary Keramidas on 1 Apr 2010 23:37 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" <ILoveMyCorgi(a)discussions.microsoft.com> wrote in message news:36203422-F3C3-48FF-A1BE-F6A763561362(a)microsoft.com... >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!
From: JLGWhiz on 2 Apr 2010 19:39 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 news:5v9ar5lvsaj609hcpsf2fg5ovm5bam1pda(a)4ax.com... > 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 > <ILoveMyCorgi(a)discussions.microsoft.com> 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 >>> <ILoveMyCorgi(a)discussions.microsoft.com> 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! >>> >>> . >>> >
First
|
Prev
|
Pages: 1 2 3 Prev: XML and SharePoint Next: Save a date as a variable and run/convert formula against the vari |