Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Autofilter
A B
1 100 2 200 3 100 Example, I have the above records, after I used the Autofilter, with column B = 100. A B 1 100 3 100 How can I use VB to extract value from column A (visible) one by one...returning value 1 & 3 only. -- Lucotus |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Autofilter
Hi,
When you say extract, what do you mean. Will a copy of the visible cells in column A be ok, or do you need the values put in a variable? It is pretty easy to copy and paste the values somewhere else, but it is harder to put the values into variables. Thanks, "lucotuslim" wrote: A B 1 100 2 200 3 100 Example, I have the above records, after I used the Autofilter, with column B = 100. A B 1 100 3 100 How can I use VB to extract value from column A (visible) one by one...returning value 1 & 3 only. -- Lucotus |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Autofilter
Dear David, would like to put into variables...
-- Lucotus "David" wrote: Hi, When you say extract, what do you mean. Will a copy of the visible cells in column A be ok, or do you need the values put in a variable? It is pretty easy to copy and paste the values somewhere else, but it is harder to put the values into variables. Thanks, "lucotuslim" wrote: A B 1 100 2 200 3 100 Example, I have the above records, after I used the Autofilter, with column B = 100. A B 1 100 3 100 How can I use VB to extract value from column A (visible) one by one...returning value 1 & 3 only. -- Lucotus |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Autofilter
How about something like this:
Option Explicit Sub testme() Dim wks As Worksheet Dim rngF As Range Dim myCell As Range Dim iCtr As Long Dim myArr() As Variant Set wks = Worksheets("Sheet1") With wks If .AutoFilterMode = False Then MsgBox "Please apply autofilter" Exit Sub End If If .FilterMode = False Then 'maybe??? MsgBox "Please filter something!" Exit Sub End If With .AutoFilter.Range If .Columns(1).Cells.SpecialCells(xlCellTypeVisible). Count = 1 Then 'header row only MsgBox "No details shown. Please try again" Exit Sub End If Set rngF = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _ .Cells.SpecialCells(xlCellTypeVisible) ReDim myArr(1 To rngF.Cells.Count) iCtr = 0 For Each myCell In rngF.Cells iCtr = iCtr + 1 myArr(iCtr) = myCell.Value Next myCell End With End With 'check the work If iCtr = 0 Then 'do nothing, shouldn't happen here because I used "exit sub" lots Else For iCtr = LBound(myArr) To UBound(myArr) MsgBox myArr(iCtr) & "--" & iCtr Next iCtr End If End Sub lucotuslim wrote: A B 1 100 2 200 3 100 Example, I have the above records, after I used the Autofilter, with column B = 100. A B 1 100 3 100 How can I use VB to extract value from column A (visible) one by one...returning value 1 & 3 only. -- Lucotus -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel 2007 autofilter change to 2003 autofilter functionality? | Excel Discussion (Misc queries) | |||
2007 excel autofilter back to 2003 autofilter? | Excel Discussion (Misc queries) | |||
2007 excel autofilter change back to 2003 autofilter? | Excel Discussion (Misc queries) | |||
2007 Autofilter worse than 2003 Autofilter | Excel Discussion (Misc queries) | |||
How to Sort within AutoFilter with Protection on (and AutoFilter . | Excel Discussion (Misc queries) |