How to strip out visibly blank rows

[Guest]

To the well I return...!

Here's the deal: an excel worksheet (Trial balance) is populated (approx.
3,000 rows) from a download and has 4 columns (first 3 are text and 4th is
numeric). The 1st column has values of 00, 01, 02, 03, etc. through 20. I
used the following formula to extract to seperate workbooks the rows needed
(i.e., 01 rows to spreadsheet Property A):

=IF(AND('P:\Common\Portfolio\OER\2006\[Trial balance.xls]12 31 06
TB'!$A1="01",'P:\Common\Portfolio\OER\2006\[Trial balance.xls]12 31 06
TB'!$D1<>0),'P:\Common\Portfolio\OER\2006\[Trial balance.xls]12 31 06
TB'!A1,"")


I get what I need (about 150 rows). However, I also get almost 2,900
visibly blank lines. Therefore, I humbly seek help either after the
extraction of the rows based on the first column 2 digit number or should I
change directions while navigating through the above funtion?

Grazie....molti!

 

[Bernie Deitrick]

MVD,

Sort the worksheet based on column D, and delete all the rows where column D is 0. Then sort based
on column A, and run the macro below.

HTH,
Bernie
MS Excel MVP

Sub ExportSheetsFromDatabase()
'Based on the value in the first column
Dim myCell As Range
Dim mySht As Worksheet
Dim myName As String
Dim myArea As Range

Set myArea = ActiveCell.CurrentRegion.Columns(1).Offset(1, 0).Cells

Set myArea = myArea.Resize(myArea.Rows.Count - 1, 1)

For Each myCell In myArea
On Error GoTo NoSheet
myName = Worksheets(myCell.Value).Name
GoTo SheetExists:
NoSheet:
Set mySht = Worksheets.Add
mySht.Name = myCell.Value
With myCell.CurrentRegion
.AutoFilter Field:=1, Criteria1:=myCell.Value
.SpecialCells(xlCellTypeVisible).Copy _
mySht.Range("A1")
mySht.Cells.EntireColumn.AutoFit
.AutoFilter
End With
Resume
SheetExists:
Next myCell
End Sub

 

[Guest]

Bernie,

Thanks for the help. Your solution is a great approach and one that I
personally would do. However, this spreadsheet is being designed for many
future uses and hands with different comfort and knowledge levels.

So, was trying to have reduce the risk of error and have it automated. But
again, thank you so much for your knowledge transfer!
--
MVD
San Francisco, California

MVD,

Sort the worksheet based on column D, and delete all the rows where column D is 0. Then sort based
on column A, and run the macro below.

HTH,
Bernie
MS Excel MVP

Sub ExportSheetsFromDatabase()
'Based on the value in the first column
Dim myCell As Range
Dim mySht As Worksheet
Dim myName As String
Dim myArea As Range

Set myArea = ActiveCell.CurrentRegion.Columns(1).Offset(1, 0).Cells

Set myArea = myArea.Resize(myArea.Rows.Count - 1, 1)

For Each myCell In myArea
On Error GoTo NoSheet
myName = Worksheets(myCell.Value).Name
GoTo SheetExists:
NoSheet:
Set mySht = Worksheets.Add
mySht.Name = myCell.Value
With myCell.CurrentRegion
.AutoFilter Field:=1, Criteria1:=myCell.Value
.SpecialCells(xlCellTypeVisible).Copy _
mySht.Range("A1")
mySht.Cells.EntireColumn.AutoFit
.AutoFilter
End With
Resume
SheetExists:
Next myCell
End Sub

To the well I return...!

Here's the deal: an excel worksheet (Trial balance) is populated (approx.
3,000 rows) from a download and has 4 columns (first 3 are text and 4th is
numeric). The 1st column has values of 00, 01, 02, 03, etc. through 20. I
used the following formula to extract to seperate workbooks the rows needed
(i.e., 01 rows to spreadsheet Property A):

=IF(AND('P:\Common\Portfolio\OER\2006\[Trial balance.xls]12 31 06
TB'!$A1="01",'P:\Common\Portfolio\OER\2006\[Trial balance.xls]12 31 06
TB'!$D1<>0),'P:\Common\Portfolio\OER\2006\[Trial balance.xls]12 31 06
TB'!A1,"")


I get what I need (about 150 rows). However, I also get almost 2,900
visibly blank lines. Therefore, I humbly seek help either after the
extraction of the rows based on the first column 2 digit number or should I
change directions while navigating through the above funtion?

Grazie....molti!

 

[Guest]

Bernie,

Just thought of something. I basically know zero about macros, but can a
macro be written that will take care of the sorts?
--
MVD
San Francisco, California

MVD,

Sort the worksheet based on column D, and delete all the rows where column D is 0. Then sort based
on column A, and run the macro below.

HTH,
Bernie
MS Excel MVP

Sub ExportSheetsFromDatabase()
'Based on the value in the first column
Dim myCell As Range
Dim mySht As Worksheet
Dim myName As String
Dim myArea As Range

Set myArea = ActiveCell.CurrentRegion.Columns(1).Offset(1, 0).Cells

Set myArea = myArea.Resize(myArea.Rows.Count - 1, 1)

For Each myCell In myArea
On Error GoTo NoSheet
myName = Worksheets(myCell.Value).Name
GoTo SheetExists:
NoSheet:
Set mySht = Worksheets.Add
mySht.Name = myCell.Value
With myCell.CurrentRegion
.AutoFilter Field:=1, Criteria1:=myCell.Value
.SpecialCells(xlCellTypeVisible).Copy _
mySht.Range("A1")
mySht.Cells.EntireColumn.AutoFit
.AutoFilter
End With
Resume
SheetExists:
Next myCell
End Sub

To the well I return...!

Here's the deal: an excel worksheet (Trial balance) is populated (approx.
3,000 rows) from a download and has 4 columns (first 3 are text and 4th is
numeric). The 1st column has values of 00, 01, 02, 03, etc. through 20. I
used the following formula to extract to seperate workbooks the rows needed
(i.e., 01 rows to spreadsheet Property A):

=IF(AND('P:\Common\Portfolio\OER\2006\[Trial balance.xls]12 31 06
TB'!$A1="01",'P:\Common\Portfolio\OER\2006\[Trial balance.xls]12 31 06
TB'!$D1<>0),'P:\Common\Portfolio\OER\2006\[Trial balance.xls]12 31 06
TB'!A1,"")


I get what I need (about 150 rows). However, I also get almost 2,900
visibly blank lines. Therefore, I humbly seek help either after the
extraction of the rows based on the first column 2 digit number or should I
change directions while navigating through the above funtion?

Grazie....molti!