Workbook contains multiple sheets with papers published by various authors.
Each sheet contains papers of a different status, e.g., in press, published,
with multiple authors listed on each worksheet.
I have created the following macro to pull records from multiple sheets for
a single author. I had to set the ranges at 50 lines apart because various
authors have different numbers of records. Now I'd like to delete empty
rows.
I'm sure there is a simple addition to the macro that would delete empty rows.
Can anyone help?
Thanks,
K

Sub AllWork()
'
' AllWork Macro
' Macro recorded 9/27/2004 by Kristin Kraus
'
' Keyboard Shortcut: Ctrl+Shift+L
'
Sheets("Published-2003").Cells.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("A1:A2"), CopyToRange:=Range("A5"), Unique:=False
Sheets("Published-2004").Cells.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("A1:A2"), CopyToRange:=Range("A50"),
Unique:=False
Sheets("In Press").Cells.AdvancedFilter Action:=xlFilterCopy,
CriteriaRange _
:=Range("A1:A2"), CopyToRange:=Range("A100"), Unique:=False
Sheets("Submitted").Cells.AdvancedFilter Action:=xlFilterCopy,
CriteriaRange _
:=Range("A1:A2"), CopyToRange:=Range("A150"), Unique:=False
Sheets("In Prep").Cells.AdvancedFilter Action:=xlFilterCopy,
CriteriaRange _
:=Range("A1:A2"), CopyToRange:=Range("A200"), Unique:=False
Sheets("OtherDocs").Cells.AdvancedFilter Action:=xlFilterCopy,
CriteriaRange _
:=Range("A1:A2"), CopyToRange:=Range("A250"), Unique:=False
End Sub

Hi Kristin,

You can determine programmatically the last non-empty cell in A:A and copy
the data on the row bellow, instead of copying every 50 rows.

The expression:
Range("A65536").End(XlUp)
returns the last none empty cell in cpolumn A. It is similar to going to
cell A65536 and pressing CTRL+UpArrow. That is:
Range("A65536").End(XlUp).Offset(1,0)
returns the following cell which is empty. This is where you want to copy
your new set of data at each CopyToRange.

Now, assuming you have headers in row 4, replace all
CopyToRange:=Range("A5")
CopyToRange:=Range("A50")
CopyToRange:=Range("A100")
...
By
Range("A65536").End(XlUp).Offset(1,0)

Regards,
Sebastien

"Kristin" wrote:

Workbook contains multiple sheets with papers published by various authors.
Each sheet contains papers of a different status, e.g., in press, published,
with multiple authors listed on each worksheet.
I have created the following macro to pull records from multiple sheets for
a single author. I had to set the ranges at 50 lines apart because various
authors have different numbers of records. Now I'd like to delete empty
rows.
I'm sure there is a simple addition to the macro that would delete empty rows.
Can anyone help?
Thanks,
K

Sub AllWork()
'
' AllWork Macro
' Macro recorded 9/27/2004 by Kristin Kraus
'
' Keyboard Shortcut: Ctrl+Shift+L
'
Sheets("Published-2003").Cells.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("A1:A2"), CopyToRange:=Range("A5"), Unique:=False
Sheets("Published-2004").Cells.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("A1:A2"), CopyToRange:=Range("A50"),
Unique:=False
Sheets("In Press").Cells.AdvancedFilter Action:=xlFilterCopy,
CriteriaRange _
:=Range("A1:A2"), CopyToRange:=Range("A100"), Unique:=False
Sheets("Submitted").Cells.AdvancedFilter Action:=xlFilterCopy,
CriteriaRange _
:=Range("A1:A2"), CopyToRange:=Range("A150"), Unique:=False
Sheets("In Prep").Cells.AdvancedFilter Action:=xlFilterCopy,
CriteriaRange _
:=Range("A1:A2"), CopyToRange:=Range("A200"), Unique:=False
Sheets("OtherDocs").Cells.AdvancedFilter Action:=xlFilterCopy,
CriteriaRange _
:=Range("A1:A2"), CopyToRange:=Range("A250"), Unique:=False
End Sub