Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a Range after Autofilter
Hello,
I'm very very new to Excel VBA programming and I have a question in regards to selecting Ranges after an Autofilter has been applied. The user will filter the sheet down to their criteria and then I need to loop through columns A and B and add the filtered data to an array. Here the code I have so far: Dim LastRow Dim b() Range("A65536").End(xlUp).Select LastRow = ActiveCell.Row b = Range("A2:B2" & LastRow).SpecialCells(xlCellTypeVisible).value The problem is that it's not selecting all the values. I'm assuming a thick gray line between row numbers in an autofilter indicates a separate range. It only pulls the data from the "first" range. How do I select all the ranges into one? and assign it to b? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a Range after Autofilter
I suspect if autofilter is on then you are going to have to use
something like: Dim rng As Range Set rng = ActiveSheet.AutoFilter.Range Regards J On 20 Nov, 13:13, "Mike Williams" wrote: Hello, I'm very very new to Excel VBA programming and I have a question in regards to selecting Ranges after an Autofilter has been applied. The user will filter the sheet down to their criteria and then I need to loop through columns A and B and add the filtered data to an array. Here the code I have so far: Dim LastRow Dim b() Range("A65536").End(xlUp).Select LastRow = ActiveCell.Row b = Range("A2:B2" & LastRow).SpecialCells(xlCellTypeVisible).value The problem is that it's not selecting all the values. I'm assuming a thick gray line between row numbers in an autofilter indicates a separate range. It only pulls the data from the "first" range. How do I select all the ranges into one? and assign it to b? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a Range after Autofilter
I'm not quite sure how your concatenating, but maybe this will help:
Option Explicit Sub testme() Dim rngF As Range Dim rngV As Range Dim myCell As Range Dim myArr() As String Dim iCtr As Long With ActiveSheet Set rngF = .AutoFilter.Range If rngF.Columns(1).Cells.SpecialCells(xlCellTypeVisib le) _ .Cells.Count = 1 Then MsgBox "only the header is shown" Exit Sub End If End With With rngF 'ignore the header from the count and come down one row Set rngV = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _ .Cells.SpecialCells(xlCellTypeVisible) End With ReDim myArr(1 To rngV.Cells.Count) iCtr = 0 For Each myCell In rngV.Cells iCtr = iCtr + 1 myArr(iCtr) = myCell.Value & "," & myCell.Offset(0, 1).Value Next myCell End Sub Mike Williams wrote: Thanks but I don't quite understand what to do with that. Let me explain about what I'm trying to do a little more. Column A contains E-mail Addresses and Column B contains the e-mail format for that e-mail address in Column A. So the data looks like this Column A ======== Column B ======== HTML So when the end-user goes through and finishes filtering the data to their specifications, they click a button and I need to write code that gets the values from Column A and B from the visible data. I had used Dim LastRow Dim arr1(), b() As Variant Range("A65536").End(xlUp).Select LastRow = ActiveCell.Row b = Range("A2:B2" & LastRow).SpecialCells(xlCellTypeVisible).value ReDim arr1(1 To UBound(b)) For i = 1 To UBound(b) arr1(i) = ((b(i, 1) + b(i, 2))) Next This takes the values from Column A and B and concatenates them together and adds them to the array. I then do further processing of the array in another sub. This only seems to work for the first "range" (not sure if thats the correct term) because any data after the first thick gray line (under the row number) doesn't show up in my code. I'm sure the code you gave me would help me but I don't understand how to apply it to what I already have. Thanks for your help and the help for those who respond. Mike WhytheQ wrote: I suspect if autofilter is on then you are going to have to use something like: Dim rng As Range Set rng = ActiveSheet.AutoFilter.Range Regards J On 20 Nov, 13:13, "Mike Williams" wrote: Hello, I'm very very new to Excel VBA programming and I have a question in regards to selecting Ranges after an Autofilter has been applied. The user will filter the sheet down to their criteria and then I need to loop through columns A and B and add the filtered data to an array. Here the code I have so far: Dim LastRow Dim b() Range("A65536").End(xlUp).Select LastRow = ActiveCell.Row b = Range("A2:B2" & LastRow).SpecialCells(xlCellTypeVisible).value The problem is that it's not selecting all the values. I'm assuming a thick gray line between row numbers in an autofilter indicates a separate range. It only pulls the data from the "first" range. How do I select all the ranges into one? and assign it to b? -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a Range after Autofilter
Awesome Dave!
Thank you very much! I think I'll have to get a Excel VBA reference guide or something. I have no idea what resize does. Thanks for your help! Mike Dave Peterson wrote: I'm not quite sure how your concatenating, but maybe this will help: Option Explicit Sub testme() Dim rngF As Range Dim rngV As Range Dim myCell As Range Dim myArr() As String Dim iCtr As Long With ActiveSheet Set rngF = .AutoFilter.Range If rngF.Columns(1).Cells.SpecialCells(xlCellTypeVisib le) _ .Cells.Count = 1 Then MsgBox "only the header is shown" Exit Sub End If End With With rngF 'ignore the header from the count and come down one row Set rngV = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _ .Cells.SpecialCells(xlCellTypeVisible) End With ReDim myArr(1 To rngV.Cells.Count) iCtr = 0 For Each myCell In rngV.Cells iCtr = iCtr + 1 myArr(iCtr) = myCell.Value & "," & myCell.Offset(0, 1).Value Next myCell End Sub Mike Williams wrote: Thanks but I don't quite understand what to do with that. Let me explain about what I'm trying to do a little more. Column A contains E-mail Addresses and Column B contains the e-mail format for that e-mail address in Column A. So the data looks like this Column A ======== Column B ======== HTML So when the end-user goes through and finishes filtering the data to their specifications, they click a button and I need to write code that gets the values from Column A and B from the visible data. I had used Dim LastRow Dim arr1(), b() As Variant Range("A65536").End(xlUp).Select LastRow = ActiveCell.Row b = Range("A2:B2" & LastRow).SpecialCells(xlCellTypeVisible).value ReDim arr1(1 To UBound(b)) For i = 1 To UBound(b) arr1(i) = ((b(i, 1) + b(i, 2))) Next This takes the values from Column A and B and concatenates them together and adds them to the array. I then do further processing of the array in another sub. This only seems to work for the first "range" (not sure if thats the correct term) because any data after the first thick gray line (under the row number) doesn't show up in my code. I'm sure the code you gave me would help me but I don't understand how to apply it to what I already have. Thanks for your help and the help for those who respond. Mike WhytheQ wrote: I suspect if autofilter is on then you are going to have to use something like: Dim rng As Range Set rng = ActiveSheet.AutoFilter.Range Regards J On 20 Nov, 13:13, "Mike Williams" wrote: Hello, I'm very very new to Excel VBA programming and I have a question in regards to selecting Ranges after an Autofilter has been applied. The user will filter the sheet down to their criteria and then I need to loop through columns A and B and add the filtered data to an array. Here the code I have so far: Dim LastRow Dim b() Range("A65536").End(xlUp).Select LastRow = ActiveCell.Row b = Range("A2:B2" & LastRow).SpecialCells(xlCellTypeVisible).value The problem is that it's not selecting all the values. I'm assuming a thick gray line between row numbers in an autofilter indicates a separate range. It only pulls the data from the "first" range. How do I select all the ranges into one? and assign it to b? -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a Range after Autofilter
VBA's help may help <bg.
If you start with a range that's one cell and resize it to 12 rows by 9 columns: Dim myRng as range dim myCell as range set mycell = activesheet.range("a1") set myrng = mycell.resize(12,9) Mike Williams wrote: Awesome Dave! Thank you very much! I think I'll have to get a Excel VBA reference guide or something. I have no idea what resize does. Thanks for your help! Mike Dave Peterson wrote: I'm not quite sure how your concatenating, but maybe this will help: Option Explicit Sub testme() Dim rngF As Range Dim rngV As Range Dim myCell As Range Dim myArr() As String Dim iCtr As Long With ActiveSheet Set rngF = .AutoFilter.Range If rngF.Columns(1).Cells.SpecialCells(xlCellTypeVisib le) _ .Cells.Count = 1 Then MsgBox "only the header is shown" Exit Sub End If End With With rngF 'ignore the header from the count and come down one row Set rngV = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _ .Cells.SpecialCells(xlCellTypeVisible) End With ReDim myArr(1 To rngV.Cells.Count) iCtr = 0 For Each myCell In rngV.Cells iCtr = iCtr + 1 myArr(iCtr) = myCell.Value & "," & myCell.Offset(0, 1).Value Next myCell End Sub Mike Williams wrote: Thanks but I don't quite understand what to do with that. Let me explain about what I'm trying to do a little more. Column A contains E-mail Addresses and Column B contains the e-mail format for that e-mail address in Column A. So the data looks like this Column A ======== Column B ======== HTML So when the end-user goes through and finishes filtering the data to their specifications, they click a button and I need to write code that gets the values from Column A and B from the visible data. I had used Dim LastRow Dim arr1(), b() As Variant Range("A65536").End(xlUp).Select LastRow = ActiveCell.Row b = Range("A2:B2" & LastRow).SpecialCells(xlCellTypeVisible).value ReDim arr1(1 To UBound(b)) For i = 1 To UBound(b) arr1(i) = ((b(i, 1) + b(i, 2))) Next This takes the values from Column A and B and concatenates them together and adds them to the array. I then do further processing of the array in another sub. This only seems to work for the first "range" (not sure if thats the correct term) because any data after the first thick gray line (under the row number) doesn't show up in my code. I'm sure the code you gave me would help me but I don't understand how to apply it to what I already have. Thanks for your help and the help for those who respond. Mike WhytheQ wrote: I suspect if autofilter is on then you are going to have to use something like: Dim rng As Range Set rng = ActiveSheet.AutoFilter.Range Regards J On 20 Nov, 13:13, "Mike Williams" wrote: Hello, I'm very very new to Excel VBA programming and I have a question in regards to selecting Ranges after an Autofilter has been applied. The user will filter the sheet down to their criteria and then I need to loop through columns A and B and add the filtered data to an array. Here the code I have so far: Dim LastRow Dim b() Range("A65536").End(xlUp).Select LastRow = ActiveCell.Row b = Range("A2:B2" & LastRow).SpecialCells(xlCellTypeVisible).value The problem is that it's not selecting all the values. I'm assuming a thick gray line between row numbers in an autofilter indicates a separate range. It only pulls the data from the "first" range. How do I select all the ranges into one? and assign it to b? -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
selecting an AutoFilter value in code | Excel Programming | |||
Selecting AutoFilter Rows | Excel Programming | |||
Selecting using autofilter | Excel Programming | |||
Selecting Filtered records only when AutoFilter is on | Excel Programming | |||
Selecting Range After AutoFilter | Excel Programming |