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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 09:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com