Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to strip out visibly blank rows
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! -- MVD San Francisco, California |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to strip out visibly blank rows
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 "Candentco Creative Collection" .com wrote in message ... 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! -- MVD San Francisco, California |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to strip out visibly blank rows
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 "Bernie Deitrick" wrote: 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 "Candentco Creative Collection" .com wrote in message ... 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! -- MVD San Francisco, California |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to strip out visibly blank rows
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 "Bernie Deitrick" wrote: 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 "Candentco Creative Collection" .com wrote in message ... 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! -- MVD San Francisco, California |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy rows of data (eliminating blank rows) from fixed layout | Excel Discussion (Misc queries) | |||
#N/A coming up in VLOOKUP when visibly the two cells are same | Excel Worksheet Functions | |||
Inserting Blank rows after every row upto 2500 rows | Excel Worksheet Functions | |||
Get number of rows that data takes up, including blank rows | Excel Worksheet Functions | |||
HOW DO I CREATE A MACRO TO STRIP OUT ROWS? | Excel Discussion (Misc queries) |