See if this will do the job. As I understand it, you
have several sheets with blank rows and you want those
blank rows deleted. If you have formulas in those rows
that produce zero length strings ("") then they will not
be deleted. If you want those deleted also, the code
will need to be modified. One other thing, the rows to
be hidden are based on the last row in column A with
data in it. If column A is not equal to the longest row,
the the code will need to be modified. Try it on a copy
before running it on the actual workbook.
Sub delEmpRw()
Dim s As Long, i As Long
For s = 1 To ThisWorkbook.Sheets.Count
With Sheets(s)
lsRw = .Cells(Rows.Count, 1).End(xlUp).Row
For i = lsRw To 2 Step -1 'Assumes Header Row
If WorksheetFunction.CountA(.Range("A" & i & ":" _
& .Cells(i, Columns.Count).End(xlToLeft).Address)) = 0 Then
.Rows(i).Delete
End If
Next
.Rows(Sheets(s).Cells(Rows.Count, 1) _
.End(xlUp).Row + 1 & ":" & Rows.Count).Hidden = True
x = 0
End With
Next
End Sub
"Lizz45ie" wrote:
> The rows that I want to delete within the workbooks are colored on each
> worksheet but they are a different row in each worksheet.
>
> The rows that I want to delete at the end of the worksheets has information
> in them but not all of the cells are completed. They have a #n/a in the
> cells where data is missing in the row. Hope that helps.
>
> "RyanH" wrote:
>
> > > I need help in deleting empty rows on multiple worksheets where the empty
> > row
> > > is a different row number on each of the worksheets.
> >
> > Are you saying you want to delete all empty rows or one specific empty row
> > each worksheet? If you want to delete one specific row on each worksheet you
> > will have to have some way of indicating to Excel where to find that row
> > number. Maybe by using a helper row or a interior color, etc.
> >
> > >I also need to delete the empty rows at the bottom of the worksheets after the >last line of data.
> >
> > I'm not quite sure why you want to delete empty rows below the last line.
> > They are empty, and if you delete them they will still remain there.
> >
> > --
> > Cheers,
> > Ryan
> >
> >
> > "Elizabeth" wrote:
> >
> > > I need help in deleting empty rows on multiple worksheets where the empty row
> > > is a different row number on each of the worksheets. I also need to delete
> > > the empty rows at the bottom of the worksheets after the last line of data.
|
Similar Threads
