| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying filtered rows
After I filter list I want to copy only the visible rows but with the below
code it copies the hidden rows. also Private Sub CommandButton1_Click() ' Analyze records Dim c As Range Dim ws As Worksheet Dim iEnd As Long Dim ws1 As Worksheet UndoMyFilter Set ws = Sheets("Summary") iEnd = ws.Range("B7").End(xlDown).Row For Each c In ws.Range("B7:B" & iEnd) If c < ws.Range("G1") Then c.EntireRow.Hidden = True Next c For Each c In ws.Range("B26:B" & iEnd) If c ws.Range("G2") Then c.EntireRow.Hidden = True Next c For Each c In ws.Range("B7:B" & iEnd) If c.Offset(0, 2) < ws.Range("G3") Then c.EntireRow.Hidden = True Next c Application.CutCopyMode = False Range("B6:F6").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("Analyze").Select Range("E36").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copying filtered rows
Here's a macro I use (often).
Sub DumpAutoFilterToNewSheet() Dim ws As Worksheet ' Your Current sheet - the sheet with the Auto-Filter Dim ws1 As Worksheet ' This will be a newly added sheet Set ws = ActiveSheet Set ws1 = Worksheets.Add ws.Activate ws.AutoFilter.Range.Copy _ Destination:=ws1.Cells(1, 1) ' Pastes into A1 - Change to suit End Sub "Oldjay" wrote: After I filter list I want to copy only the visible rows but with the below code it copies the hidden rows. also Private Sub CommandButton1_Click() ' Analyze records Dim c As Range Dim ws As Worksheet Dim iEnd As Long Dim ws1 As Worksheet UndoMyFilter Set ws = Sheets("Summary") iEnd = ws.Range("B7").End(xlDown).Row For Each c In ws.Range("B7:B" & iEnd) If c < ws.Range("G1") Then c.EntireRow.Hidden = True Next c For Each c In ws.Range("B26:B" & iEnd) If c ws.Range("G2") Then c.EntireRow.Hidden = True Next c For Each c In ws.Range("B7:B" & iEnd) If c.Offset(0, 2) < ws.Range("G3") Then c.EntireRow.Hidden = True Next c Application.CutCopyMode = False Range("B6:F6").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("Analyze").Select Range("E36").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copying filtered rows
When I copy
ws.Activate ws.AutoFilter.Range.Copy _ Destination:=ws1.Cells(36, 5) I get an error Note I am not auto filtering . I am just hiding rows and then trying to copy the visible rows to another sheet "JMay" wrote: Here's a macro I use (often). Sub DumpAutoFilterToNewSheet() Dim ws As Worksheet ' Your Current sheet - the sheet with the Auto-Filter Dim ws1 As Worksheet ' This will be a newly added sheet Set ws = ActiveSheet Set ws1 = Worksheets.Add ws.Activate ws.AutoFilter.Range.Copy _ Destination:=ws1.Cells(1, 1) ' Pastes into A1 - Change to suit End Sub "Oldjay" wrote: After I filter list I want to copy only the visible rows but with the below code it copies the hidden rows. also Private Sub CommandButton1_Click() ' Analyze records Dim c As Range Dim ws As Worksheet Dim iEnd As Long Dim ws1 As Worksheet UndoMyFilter Set ws = Sheets("Summary") iEnd = ws.Range("B7").End(xlDown).Row For Each c In ws.Range("B7:B" & iEnd) If c < ws.Range("G1") Then c.EntireRow.Hidden = True Next c For Each c In ws.Range("B26:B" & iEnd) If c ws.Range("G2") Then c.EntireRow.Hidden = True Next c For Each c In ws.Range("B7:B" & iEnd) If c.Offset(0, 2) < ws.Range("G3") Then c.EntireRow.Hidden = True Next c Application.CutCopyMode = False Range("B6:F6").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("Analyze").Select Range("E36").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copying filtered rows
Thanks for the help
"Oldjay" wrote: When I copy ws.Activate ws.AutoFilter.Range.Copy _ Destination:=ws1.Cells(36, 5) I get an error Note I am not auto filtering . I am just hiding rows and then trying to copy the visible rows to another sheet "JMay" wrote: Here's a macro I use (often). Sub DumpAutoFilterToNewSheet() Dim ws As Worksheet ' Your Current sheet - the sheet with the Auto-Filter Dim ws1 As Worksheet ' This will be a newly added sheet Set ws = ActiveSheet Set ws1 = Worksheets.Add ws.Activate ws.AutoFilter.Range.Copy _ Destination:=ws1.Cells(1, 1) ' Pastes into A1 - Change to suit End Sub "Oldjay" wrote: After I filter list I want to copy only the visible rows but with the below code it copies the hidden rows. also Private Sub CommandButton1_Click() ' Analyze records Dim c As Range Dim ws As Worksheet Dim iEnd As Long Dim ws1 As Worksheet UndoMyFilter Set ws = Sheets("Summary") iEnd = ws.Range("B7").End(xlDown).Row For Each c In ws.Range("B7:B" & iEnd) If c < ws.Range("G1") Then c.EntireRow.Hidden = True Next c For Each c In ws.Range("B26:B" & iEnd) If c ws.Range("G2") Then c.EntireRow.Hidden = True Next c For Each c In ws.Range("B7:B" & iEnd) If c.Offset(0, 2) < ws.Range("G3") Then c.EntireRow.Hidden = True Next c Application.CutCopyMode = False Range("B6:F6").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("Analyze").Select Range("E36").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copying filtered rows
Well using auto-filter would certainly be easier (on you), even if you just
created a new column (at the far right - last column) entitled Select? and include this in the auto-filter table list - Just enter a "Y" (w/o the " "'s) in the rows you wish to be visible and copy; then presto, and use my suggested macro. "Oldjay" wrote: When I copy ws.Activate ws.AutoFilter.Range.Copy _ Destination:=ws1.Cells(36, 5) I get an error Note I am not auto filtering . I am just hiding rows and then trying to copy the visible rows to another sheet "JMay" wrote: Here's a macro I use (often). Sub DumpAutoFilterToNewSheet() Dim ws As Worksheet ' Your Current sheet - the sheet with the Auto-Filter Dim ws1 As Worksheet ' This will be a newly added sheet Set ws = ActiveSheet Set ws1 = Worksheets.Add ws.Activate ws.AutoFilter.Range.Copy _ Destination:=ws1.Cells(1, 1) ' Pastes into A1 - Change to suit End Sub "Oldjay" wrote: After I filter list I want to copy only the visible rows but with the below code it copies the hidden rows. also Private Sub CommandButton1_Click() ' Analyze records Dim c As Range Dim ws As Worksheet Dim iEnd As Long Dim ws1 As Worksheet UndoMyFilter Set ws = Sheets("Summary") iEnd = ws.Range("B7").End(xlDown).Row For Each c In ws.Range("B7:B" & iEnd) If c < ws.Range("G1") Then c.EntireRow.Hidden = True Next c For Each c In ws.Range("B26:B" & iEnd) If c ws.Range("G2") Then c.EntireRow.Hidden = True Next c For Each c In ws.Range("B7:B" & iEnd) If c.Offset(0, 2) < ws.Range("G3") Then c.EntireRow.Hidden = True Next c Application.CutCopyMode = False Range("B6:F6").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("Analyze").Select Range("E36").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copying four filtered cells | Excel Discussion (Misc queries) | |||
| copying one column with hidden/filtered rows to another colum | Excel Discussion (Misc queries) | |||
| Copying Filtered Data | Excel Discussion (Misc queries) | |||
| Copying filtered data | Excel Programming | |||
| Filtered Visible Rows & VBA Non-Filtered Rows Displayed | Excel Programming | |||
Hybrid Mode
