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!

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" <(E-Mail Removed)> wrote in message
news:36203422-F3C3-48FF-A1BE-(E-Mail Removed)...
>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!

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" <(E-Mail Removed)> wrote in message
news:36203422-F3C3-48FF-A1BE-(E-Mail Removed)...
> 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!

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
<(E-Mail Removed)> 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!

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!

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!

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" <(E-Mail Removed)> wrote in message
> news:36203422-F3C3-48FF-A1BE-(E-Mail Removed)...
> > 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!
>
> .
>

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!

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" <(E-Mail Removed)> wrote in message
> news:36203422-F3C3-48FF-A1BE-(E-Mail Removed)...
> >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!
>
>
> .
>

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
> <(E-Mail Removed)> 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!
>
> .
>