Can someone help me?

  • Thread starter Thread starter ILoveMyCorgi
  • Start date Start date
I

ILoveMyCorgi

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
 
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?
 
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
 
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.
 
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.
 
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.
 
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.
 
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.
 
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.
 
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
 
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
 
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
 
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.
 
Back
Top