Prev: XML and SharePoint
Next: Save a date as a variable and run/convert formula against the vari
From: Mike H on 1 Apr 2010 15:06
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!
From: ILoveMyCorgi on 1 Apr 2010 18:17
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: ILoveMyCorgi on 1 Apr 2010 18:53
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!
From: ILoveMyCorgi on 1 Apr 2010 18:54
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 Insert>Module. To run the
> macro, in Excel select Tools>Macro>Macros 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" <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: ILoveMyCorgi on 1 Apr 2010 18:55
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  |  Next  |  Last
Pages: 1 2 3
Prev: XML and SharePoint
Next: Save a date as a variable and run/convert formula against the vari