Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula help
I have a workbook set up jan - dec, i want get info from these pages consolidated onto1 page. I need the rows that have certain blank colums only. example, jan has 5 rows of names, row 3 & 5 are the only ones that have column f thru h blank. these are the only rows i want on new page need help w/formula |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula help
Susan
A formula isn't going to do what you want. You will need VBA programming for this. The macro below will do it for you. I assumed that the sheet you refer to as the "new page" is named "New Sht". This is hard-coded in the macro. Change it in the code as you see fit. I also assumed that the "New Sht" is the only sheet in the workbook besides the jan-dec sheets. In other words, this macro will copy from every sheet in the workbook except the "New Sht". I also assumed the data started in Column A in row 2 on every sheet. Watch out for line wrapping in this message. View this message in full screen only. HTH Otto Sub CopyFH() Dim ws As Worksheet Dim rColA As Range Dim i As Range Dim Dest As Range Set Dest = Sheets("New Sht").Range("A" & Rows.Count).End(xlUp).Offset(1) For Each ws In ThisWorkbook.Worksheets If ws.Name < "New Sht" Then With ws Set rColA = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) For Each i In rColA If Application.CountA(.Range(.Cells(i.Row, 6), .Cells(i.Row, 8))) = 0 Then ..Range(.Cells(i.Row, 1), .Cells(i.Row, Columns.Count).End(xlToLeft)).Copy Dest Set Dest = Dest.Offset(1) End If Next i End With End If Next ws End Sub "susanm" wrote in message ... I have a workbook set up jan - dec, i want get info from these pages consolidated onto1 page. I need the rows that have certain blank colums only. example, jan has 5 rows of names, row 3 & 5 are the only ones that have column f thru h blank. these are the only rows i want on new page need help w/formula |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|