Is there anyway that you can fill in the blanks with multiple
sheets? I understand that I can use go to special and choose blank
to fill the blanks, but is there a way that I can fill multiple sheets
all in once? Anybody has a macro like that? Thanks!

Hi Cindy,

What do you mean by 'fill in the blanks' ?

Do you mean all the blank cells on a sheet or in a column or a row or a range?

If you know how write the code to fill in the blanks for your purposes, you could loop thru all the worksheets in your workbook and fill in the blanks for each one in turn.

If you already have a macro that finds the rows to be deleted, why not delete them immediately rather than mark them and delete them later??

deutz presented the following explanation :
Hi and thanks in advance,

I am using Excel 2003

I have a delete rows macro that tests whether there is data in one or
more user defined columns on the sheet and if not, places an "X" in the
first blank column to the right of the data. I then apply an autofilter
to pick up all rows with an "X" and then would like to delete all the
filtered rows except the first one in the range which is a header row.
The first row of data may be any row on the sheet as defined by the user
via a userform.

The code below works fine if there is no data in row 65,536, the last
row on the worksheet. If there is a value in the last row then the macro
does not delete the filtered rows as required. When the user defines a
range that includes all rows on the sheet, from 1 to 65,536, I think
Excel changes the range address to a column reference eg. "A : D"
instead of the full address "A1 : D65536" so that when you apply the
offset method it offsets to the wrong row. Any ideas?

With rRange
.AutoFilter Field:=mLastCol + 1, Criteria1:="X"
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delet e
End With

Why not...?

Instead of placing an 'x' in the column to the right, simply delete the
row right then and there, saving the extra step of filtering. I
recommend, though, that you start at the last row containing data and
work up from there so the deleted rows don't shift into your current
position in your loop. (Assumes you're looping through the rows of data
via 'For...' or 'For Each...')

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Give us an idea of what columns or ranges would be involved on each of
the multiple sheets.

What would you fill the blanks with? The value above? Or something
else?

This macro from Dave Peterson......revised by me to loop through all
sheets will fill in the blanks in Column A with the value from the
cell above.

Sub Fill_Blanks()
'by Dave Peterson 2004-01-06
'fill blank cells in column with value above
Dim wks As Worksheet
Dim rng As Range
Dim lastrow As Long
Dim Col As Long

For Each wks In ActiveWorkbook.Worksheets
With wks
Col = .Range("A1").Column

Set rng = .UsedRange 'try to reset the lastcell
lastrow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set rng = Nothing
On Error Resume Next
Set rng = .Range(.Cells(2, Col), .Cells(lastrow, Col)) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If rng Is Nothing Then
MsgBox "No blanks found"
Exit Sub
Else
rng.NumberFormat = "General"
rng.FormulaR1C1 = "=R[-1]C"
End If

'replace formulas with values
With .Cells(1, Col).EntireColumn
.Value = .Value
End With

End With
Next wks
End Sub


Gord

On Tue, 22 May 2012 15:28:46 -0700 (PDT), Cindy Wang
wrote:

Is there anyway that you can fill in the blanks with multiple
sheets? I understand that I can use go to special and choose blank
to fill the blanks, but is there a way that I can fill multiple sheets
all in once? Anybody has a macro like that? Thanks!

You cannot Offset(1, 0) from row 65536


Gord

On Wed, 23 May 2012 03:21:35 +0000, deutz
wrote:


Hi and thanks in advance,

I am using Excel 2003

I have a delete rows macro that tests whether there is data in one or
more user defined columns on the sheet and if not, places an "X" in the
first blank column to the right of the data. I then apply an autofilter
to pick up all rows with an "X" and then would like to delete all the
filtered rows except the first one in the range which is a header row.
The first row of data may be any row on the sheet as defined by the user
via a userform.

The code below works fine if there is no data in row 65,536, the last
row on the worksheet. If there is a value in the last row then the macro
does not delete the filtered rows as required. When the user defines a
range that includes all rows on the sheet, from 1 to 65,536, I think
Excel changes the range address to a column reference eg. "A : D"
instead of the full address "A1 : D65536" so that when you apply the
offset method it offsets to the wrong row. Any ideas?

With rRange
AutoFilter Field:=mLastCol + 1, Criteria1:="X"
Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delet e
End With

Thanks for your suggestions. Yes, that makes sense now that you put it like that ... there is no row 65537 to offset to. I did intially have some code that did not use a filter but looped thru the rows and deleted from the bottom up ... however, this proved a bit too slow, hence the use of a filter. Is there another way I can delete all filtered rows that will not crash on row 65536?