PDA

View Full Version : Solved: Delete blank rows.



alopecito08
11-06-2008, 12:13 PM
I'm working on this Macro, I need some help. I have a database with an estimate of 850 rows, When I filter the database, I did create a Macro to separate the rows depending on certain criteria, and then copy and paste the filtered info on a new sheet...Problem: When I run the macro the new Sheet has 64000 rows!, so I go from 35 KB to 25 MG!...I need either to add a new macro to DELETE all those blank rows or PASTE the Filtered Info in a way that won't paste the blank rows...Copy and Paste Special won't work!...

Thanks for your help!

Bob Phillips
11-06-2008, 12:15 PM
Show us your current code, it is difficult to see how you end up where you do.

alopecito08
11-06-2008, 12:17 PM
This is the Macro I'm Using to filter and Copy and Paste the info:


Sub FINALTABS2()
Sheets("LS Sales").Select
Selection.AutoFilter Field:=3, Criteria1:="*Under Ground Cash Secrets*", Operator:=xlOr _
, Criteria2:="*InfoUSA*"
routine2 ("UGCS Sales")

Cells.Select
Cells.EntireColumn.AutoFit
Cells.EntireRow.AutoFit

Sheets("LS Sales").Select
Selection.AutoFilter Field:=3, Criteria1:="*Stephen Pierce*", Operator:=xlOr _
, Criteria2:="*InfoUSA*"
routine2 ("SP Sales")

Cells.Select
Cells.EntireColumn.AutoFit
Cells.EntireRow.AutoFit

Sheets("LS Sales").Select
Selection.AutoFilter Field:=3, Criteria1:="*Rich Jerk*", Operator:=xlOr _
, Criteria2:="*InfoUSA*"
routine2 ("RJ Sales")

Cells.Select
Cells.EntireColumn.AutoFit
Cells.EntireRow.AutoFit

Sheets("LS Sales").Select
Selection.AutoFilter Field:=3, Criteria1:="*Thrive*", Operator:=xlOr _
, Criteria2:="*InfoUSA*"
routine2 ("Thrv Sales ")

Cells.Select
Cells.EntireColumn.AutoFit
Cells.EntireRow.AutoFit

End Sub




Function routine(nombre)
Columns("A:I").Copy
Sheets.Add
ActiveSheet.Paste
ActiveSheet.Name = nombre
End Function
Function routine1(nombre)
Columns("A:J").Copy
Sheets.Add
ActiveSheet.Paste
ActiveSheet.Name = nombre
End Function
Function routine2(nombre)
Columns("A:F").Copy
Sheets.Add
ActiveSheet.Paste
ActiveSheet.Name = nombre
End Function

mdmackillop
11-06-2008, 01:06 PM
Can you post some sample data? Use Manage Attachments in the Go Advanced reply section.

alopecito08
11-06-2008, 01:19 PM
This is one example of the database I'm managing...

Thanks

mdmackillop
11-06-2008, 01:31 PM
Function routine(nombre)
Intersect(Columns("A:I"), ActiveSheet.UsedRange).Copy
Sheets.Add
ActiveSheet.Paste
ActiveSheet.Name = nombre
End Function


etc.

mdmackillop
11-06-2008, 01:34 PM
BTW, No need to select Cells


'Cells.Select
Cells.EntireColumn.AutoFit
Cells.EntireRow.AutoFit

alopecito08
11-06-2008, 01:42 PM
It works!....You're the best!

Thanks