Prev: XML and SharePoint
Next: Save a date as a variable and run/convert formula against the vari
From: ILoveMyCorgi on 1 Apr 2010 14:13
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 1 Apr 2010 14:38
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: Rick Rothstein on 1 Apr 2010 14:40
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 14:48
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: Mike H on 1 Apr 2010 15:03
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!
 |  Next  |  Last
Pages: 1 2 3
Prev: XML and SharePoint
Next: Save a date as a variable and run/convert formula against the vari