Delete empty whole row if range B:I doesn't contain data.

[hurcuma]

Hi

I have a spreadsheet that contains the following.

	A	B	C	D	E	F	G	H	I
1	Staff ID	12345	56789	23456	7653	5554	88887	333464	2223
2	Last Name	Jones	Smith	Briggs	Townsend	Dale	Stone	Lavell	Trigg
3	Firstname	Andrew	Mary	James	Tania	Chip	Dale	Richard	Beth
4	Start Date		01/11/17		10/11/17		14/11/17	16/11/17	18/01/17
5	Pay Grade	G1	G1	G2	G1	G3	G3	G4	G1
6	Phone Type
7	Address type			Home		Work
8	Comp		1000		800	600	200	400	500
9	Bonus		100		100		100	100

<tbody>
</tbody>

Could someone help me work out how I can get excel to review the contents of the table, from Cols B to I.
Then within that range if the rest of the row doesn't contain data, then delete the whole of that row.

So in this example above, Row 6 would disappear.

My "live" spreadsheet has 121 rows, but not all of them have data in, so I want to extract only those that have data.

Many thanks in advance for any help.

 

[mumps]

Try:

Sub delRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim x As Long
    For x = LastRow To 2 Step -1
        If WorksheetFunction.CountA(Range("B" & x & ":I" & x)) = 0 Then
            Rows(x).EntireRow.Delete
        End If
    Next x
    Application.ScreenUpdating = True
End Sub

 

[hurcuma]

Hi Mumps.

that is amazing, it works perfect, I have been searching google for several days.

Could I ask one extra question
The actual file is a download from our hr database, and it populates each employee either a starter or a change, so one month you might go to Column I then next month it could be Column AJ etc.

The first column to look in will always be Col B. But as the last column could vary is there a way to amend the vba code to the last entry in Row 2?

Regards

 

[mumps]

Here you go!

Sub delRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim lColumn As Long
    lColumn = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column
    Dim x As Long
    For x = LastRow To 2 Step -1
        If WorksheetFunction.CountA(Range(Cells(x, 2), Cells(x, lColumn))) = 0 Then
            Rows(x).EntireRow.Delete
        End If
    Next x
    Application.ScreenUpdating = True
End Sub

 

[hurcuma]

Hi Mumps.

Thank you for coming back with a solution so quick once more, but I can't seem to get the second vba that you have posted to work where it is looking for the last cell in row 2 to work.

After inserting the macro, and running it, no effect is happening, the original data stays in tact.

Any thoughts?

and many thanks again for your help.

 

[Rick Rothstein]

See if this macro works for you...

[table="width: 500"]
[tr]
	[td]Sub DeleteBlankDataRows()
  Dim LastRow As Long, ColCount As String
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  ColCount = Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column - 1
  Range("A1:A" & LastRow) = Evaluate(Replace(Replace("IF(COUNTBLANK(OFFSET(A1:A%,ROW(A1:A%)-1,1,1,@))=@,""#N/A"",A1:A%)", "@", ColCount), "%", LastRow))
  On Error GoTo NoBlankRows
  Columns("A").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
NoBlankRows:
End Sub[/td]
[/tr]
[/table]

 

[hurcuma]

Hi Rick, thank you for taking a look at my problem.

Once running your macro, I am getting no rows deleted.

Mumps VBA works great, and I wondered as a simple next step is it possible for me to just visually look at the last column and then run the macro and a pop says enter last column e.g. AJ and then continue with rest of the macro?

 

[Rick Rothstein]

Hi Rick, thank you for taking a look at my problem.

Once running your macro, I am getting no rows deleted.

Do you have any formulas on the worksheet? If so, what columns are they in?

 

[hurcuma]

Hi Rick

Yes, my last column has a countblank formula in each cell.
I can do without this column if that helps.

Other than that, all the cells are without formulas.

 

[mumps]

Maybe:

Sub delRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim response As String
    response = InputBox("Please enter the last column.")
    Dim x As Long
    For x = LastRow To 2 Step -1
        If WorksheetFunction.CountA(Range(Cells(x, 2), Cells(x, response))) = 0 Then
            Rows(x).EntireRow.Delete
        End If
    Next x
    Application.ScreenUpdating = True
End Sub