| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filtered rows not in code???
I have a spreadsheet with thousands of rows that I frequently filter
(autofilter). I then have some code that I would like to run just on the filtered(showing) rows that I am selecting... meaning if I have rows 2-10 selected but only rows 2 and 5 are showing due to my filter... how do I perform my code just on those two rows??? I was trying... If Selection.Rows.Count 1 Then For i = 0 To Selection.Rows.Count - 1 Step 1 but this would perform the operation on all the rows (i.e. 2-10) any thoughts? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Filtered rows not in code???
look in the vba help index for special cells visible
-- Don Guillett SalesAid Software "John" wrote in message ... I have a spreadsheet with thousands of rows that I frequently filter (autofilter). I then have some code that I would like to run just on the filtered(showing) rows that I am selecting... meaning if I have rows 2-10 selected but only rows 2 and 5 are showing due to my filter... how do I perform my code just on those two rows??? I was trying... If Selection.Rows.Count 1 Then For i = 0 To Selection.Rows.Count - 1 Step 1 but this would perform the operation on all the rows (i.e. 2-10) any thoughts? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Filtered rows not in code???
Thanks for the reply... something like this???
If cp.Range("a" & rw1 + i).SpecialCells(xlCellTypeVisible) = False Then GoTo next1: Else this doesn't quite work "Don Guillett" wrote: look in the vba help index for special cells visible -- Don Guillett SalesAid Software "John" wrote in message ... I have a spreadsheet with thousands of rows that I frequently filter (autofilter). I then have some code that I would like to run just on the filtered(showing) rows that I am selecting... meaning if I have rows 2-10 selected but only rows 2 and 5 are showing due to my filter... how do I perform my code just on those two rows??? I was trying... If Selection.Rows.Count 1 Then For i = 0 To Selection.Rows.Count - 1 Step 1 but this would perform the operation on all the rows (i.e. 2-10) any thoughts? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Filtered rows not in code???
well ideally I would like to not copy everything in the row... I would like
to do this... For i = 0 To Selection.Rows.Count - 1 Step 1 If cp.Range("a" & rw1 + i).Cells.SpecialCells(xlCellTypeVisible) < 12 Then GoTo next1: Else em.Select Range("a2000").Select Selection.End(xlUp).Select ActiveCell.Offset(1, 0).Select rw = ActiveCell.Row em.Range("a" & rw) = cp.Range("a" & rw1 + i) em.Range("b" & rw) = cp.Range("b" & rw1 + i) & "/" & cp.Range("c" & rw1 + i) em.Range("c" & rw) = cp.Range("f" & rw1 + i) & " " & cp.Range("o" & rw1 + i) em.Range("d" & rw) = cp.Range("g" & rw1 + i) em.Range("d" & rw) = cp.Range("g" & rw1 + i) em.Range("e" & rw) = cp.Range("h" & rw1 + i) em.Range("f" & rw) = cp.Range("j" & rw1 + i) em.Range("g" & rw) = cp.Range("k" & rw1 + i) em.Range("h" & rw) = cp.Range("l" & rw1 + i) em.Range("i" & rw) = cp.Range("m" & rw1 + i) next1: End If Next Appreciate you taking a look "Don Guillett" wrote: without seing your data, I think you want to copy all at once instead of one a time??? range("a2:x30").).SpecialCells(xlCellTypeVisible). copy range("aa1") -- Don Guillett SalesAid Software "John" wrote in message ... Thanks for the reply... something like this??? If cp.Range("a" & rw1 + i).SpecialCells(xlCellTypeVisible) = False Then GoTo next1: Else this doesn't quite work "Don Guillett" wrote: look in the vba help index for special cells visible -- Don Guillett SalesAid Software "John" wrote in message ... I have a spreadsheet with thousands of rows that I frequently filter (autofilter). I then have some code that I would like to run just on the filtered(showing) rows that I am selecting... meaning if I have rows 2-10 selected but only rows 2 and 5 are showing due to my filter... how do I perform my code just on those two rows??? I was trying... If Selection.Rows.Count 1 Then For i = 0 To Selection.Rows.Count - 1 Step 1 but this would perform the operation on all the rows (i.e. 2-10) any thoughts? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Filtered rows not in code???
You could always copy the whole row to col F and then cut/paste f&g to a -- Don Guillett SalesAid Software "John" wrote in message ... well ideally I would like to not copy everything in the row... I would like to do this... For i = 0 To Selection.Rows.Count - 1 Step 1 If cp.Range("a" & rw1 + i).Cells.SpecialCells(xlCellTypeVisible) < 12 Then GoTo next1: Else em.Select Range("a2000").Select Selection.End(xlUp).Select ActiveCell.Offset(1, 0).Select rw = ActiveCell.Row em.Range("a" & rw) = cp.Range("a" & rw1 + i) em.Range("b" & rw) = cp.Range("b" & rw1 + i) & "/" & cp.Range("c" & rw1 + i) em.Range("c" & rw) = cp.Range("f" & rw1 + i) & " " & cp.Range("o" & rw1 + i) em.Range("d" & rw) = cp.Range("g" & rw1 + i) em.Range("d" & rw) = cp.Range("g" & rw1 + i) em.Range("e" & rw) = cp.Range("h" & rw1 + i) em.Range("f" & rw) = cp.Range("j" & rw1 + i) em.Range("g" & rw) = cp.Range("k" & rw1 + i) em.Range("h" & rw) = cp.Range("l" & rw1 + i) em.Range("i" & rw) = cp.Range("m" & rw1 + i) next1: End If Next Appreciate you taking a look "Don Guillett" wrote: without seing your data, I think you want to copy all at once instead of one a time??? range("a2:x30").).SpecialCells(xlCellTypeVisible). copy range("aa1") -- Don Guillett SalesAid Software "John" wrote in message ... Thanks for the reply... something like this??? If cp.Range("a" & rw1 + i).SpecialCells(xlCellTypeVisible) = False Then GoTo next1: Else this doesn't quite work "Don Guillett" wrote: look in the vba help index for special cells visible -- Don Guillett SalesAid Software "John" wrote in message ... I have a spreadsheet with thousands of rows that I frequently filter (autofilter). I then have some code that I would like to run just on the filtered(showing) rows that I am selecting... meaning if I have rows 2-10 selected but only rows 2 and 5 are showing due to my filter... how do I perform my code just on those two rows??? I was trying... If Selection.Rows.Count 1 Then For i = 0 To Selection.Rows.Count - 1 Step 1 but this would perform the operation on all the rows (i.e. 2-10) any thoughts? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Cut filtered rows, paste into next empty row of new sheet, and delete cut rows | Excel Worksheet Functions | |||
| Code for Printing All Filtered Rows Separate | Excel Programming | |||
| Help!! I have problem deleting 2500 rows of filtered rows!!!! | Excel Discussion (Misc queries) | |||
| Help!!! I have problem deleting 2500 rows of filtered rows | Excel Programming | |||
| Filtered Visible Rows & VBA Non-Filtered Rows Displayed | Excel Programming | |||
Hybrid Mode
