Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Visible data entries
A problem exists for me that I can't fathom. Hoping for some help. Using
xl 2007 with Vista I am trying to enter data in a range of a filtered Sheet. When I enter this data, sometimes (not all the time) it populates the hidden rows in Range "cat" which I don,t want to happen. My Header for filtering is A3:AL. My data is in A4:AL2146 (expandable) This is some code that doesn't allways work how I want it to. Selection.AutoFilter Field:=28, Criteria1:="200" Selection.AutoFilter Field:=10, Criteria1:="3.0" Selection.AutoFilter Field:=17, Criteria1:="1" If Range("C2").Value 0 Then Range("cat") = 1 End If Cell C2 checks that there is visible cells to fill. C2 Formula is :- =SUBTOTAL(103,$A4:$A$9999) column A always has data. My range "cat" :- =OFFSET(Prepare!$AB$4,0,0,COUNT(OFFSET(Prepare!$M$ 4,0,0,9999)),1) When I run this macro to update my sheet the results sometimes over writes previously entered data in the Range "cat". Is there some code I can enter to ensure that only visible cells in Range "cat" get filled? I used to use xl 2004 11.7 on a Mac and this code allways worked properly. But alas not anymore in xl 2007 with latest updates Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Visible data entries
I have found the solution which may be of help to others
after the code line :- If Range("C2").Value 0 Then I insert this line and it solved my problems Selection.SpecialCells(xlCellTypeVisible).Select Skinman "Skinman" wrote in message ... A problem exists for me that I can't fathom. Hoping for some help. Using xl 2007 with Vista I am trying to enter data in a range of a filtered Sheet. When I enter this data, sometimes (not all the time) it populates the hidden rows in Range "cat" which I don,t want to happen. My Header for filtering is A3:AL. My data is in A4:AL2146 (expandable) This is some code that doesn't allways work how I want it to. Selection.AutoFilter Field:=28, Criteria1:="200" Selection.AutoFilter Field:=10, Criteria1:="3.0" Selection.AutoFilter Field:=17, Criteria1:="1" If Range("C2").Value 0 Then Range("cat") = 1 End If Cell C2 checks that there is visible cells to fill. C2 Formula is :- =SUBTOTAL(103,$A4:$A$9999) column A always has data. My range "cat" :- =OFFSET(Prepare!$AB$4,0,0,COUNT(OFFSET(Prepare!$M$ 4,0,0,9999)),1) When I run this macro to update my sheet the results sometimes over writes previously entered data in the Range "cat". Is there some code I can enter to ensure that only visible cells in Range "cat" get filled? I used to use xl 2004 11.7 on a Mac and this code allways worked properly. But alas not anymore in xl 2007 with latest updates Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MS2007 - deleting filtered data, deletes non-visible data too | Excel Discussion (Misc queries) | |||
Autofilter: jump from visible row to visible row by command button | Excel Programming | |||
How increase # visible entries in dropdown list box so no scroll? | New Users to Excel | |||
Worksheet has to set to visible as it is not visible after saving and closing Excel by VB. | Excel Programming | |||
Autoshapes not visible on spreadsheet but visible in print preview | Excel Discussion (Misc queries) |