Filtering to a specific row and then selecting a specific visible cell using VBA

[lakshman]

Hi all,

I have data about the equity products we release everyday. First, I want to filter for producers in my team under "Product Requestors" in Column P:

ActiveSheet.Range("$A$1:$AM$77").AutoFilter Field:=36, Criteria1:=Array( _
        "John", "Jane", "Jack"), Operator:=xlFilterValuesv

Then, I want to filter for a particular stock ticker from Column N:

ActiveSheet.Range("$A$1:$AM$77").AutoFilter Field:=14, Criteria1:="CAPL SP"

This will typically leave only one row. Within the one row, I want to select the cell that has the product's Minimum Notional Value. This falls under Column H.

Each time I do a different filter, the selected cell would naturally be different. I want this so that I can eventually use my Macro (I've already created this for another task, thanks to the wonderful MrExcel community) to copy paste this value into another sheet.

I would really appreciate it if anyone can help me out on this!

 

[frank_AL]

laksham,

Give this a try.

Dim VisRow as Long
VisRow = [COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]AutoFilter[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Offset[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]1[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]SpecialCells[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]xlCellTypeVisible[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]row[/FONT][/COLOR]

Now your data is in Range("H" & VisRow)

 

[lakshman]

Hi. Sorry I am still a novice. How do I use that? My end goal is selecting the specific cell in Column H. Thank you for replying!

 

[frank_AL]

laksham

add these Variable Declarations to the top of your code module:

Dim VisRow As Long
Dim LastRow As Long
Dim ReqValue As String

I added a line to identify the last row of your data and stored that in the LastRow variable. That is much better than hard coding as it adjusts when additional data is added. See the changes below to the Range information in your lines of code.
I then store the first Visible Row to the variable VisRow
I then store the Value in Column H of that Row to the variable ReqValue
You can then use ReqValue to populate whatever it is you need that value for.

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
ActiveSheet.Range("$A$1:$AM" & LastRow).AutoFilter Field:=36, Criteria1:=Array( _
        "John", "Jane", "Jack"), Operator:=xlFilterValuesv
ActiveSheet.Range("$A$1:$AM" & LastRow).AutoFilter Field:=14, Criteria1:="CAPL SP"
VisRow = .AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Row
ReqValue = Range("H" & VisRow).Value

 

[lakshman]

Hi thank you for following up. If I copy your code in, I get a compile error that highlights ".Autofilter" and a msgbox pops up and says invalid or unqualified reference. How do I proceed from here?

Also, a side questions, your last line of code is referring to the the final action of selecting the required cell right?

Thank you for all your help thus far!

 

[frank_AL]

laksham I will take a look at this after work today unless someone else can bail me out first!

 

[lakshman]

Haha thank you so much.

 

[frank_AL]

lahsham I've got the 'bugs' out! It is now working as expected on a test workbook I created. Yes, the last line of code sets the value of variable "ReqValue" to the content of Column H on the first visible row. You can now use that variable to write that value wherever you need to put it.

Sub visrow()
Dim rng As Range
Dim visrow As Long
Dim reqvalue As String


LastRow = Cells(Rows.Count, "AM").End(xlUp).Row
ActiveSheet.Range("$A$1:$AM" & LastRow).AutoFilter Field:=36, Criteria1:=Array("John", "Jane", "Jack"), Operator:=xlFilterValues
ActiveSheet.Range("$A$1:$AM" & LastRow).AutoFilter Field:=14, Criteria1:="CAPL SP"
Set rng = Range("AJ1")
visrow = ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Row
reqvalue = Range("H" & visrow).Value


End Sub

 

[lakshman]

Hey thank you for help. I modified the code more and I have this thus far. But I need one final help.

Sub ELNParametersCopyPaste()

Dim csv As Workbook
Dim chartbuilder As Workbook
Dim pdttype As Worksheet
Dim rngDest As Range
Dim copyRange As Range
Dim lastRow As Long


Set chartbuilder = Workbooks("Write Up Generator (5 Jan) with Tracker & Parameters Update.xlsm")
Set csv = Workbooks("ideas 9 june 17 - TEST.xlsx")
csv.Activate


' turn off any autofilters that are already set
csv.Activate
ActiveSheet.AutoFilterMode = False


lastRow = Cells(Rows.Count, "AM").End(xlUp).Row
ActiveSheet.Range("$A$1:$AM" & lastRow).AutoFilter Field:=36, Criteria1:=Array("John", "Jane", "Jack"), Operator:=xlFilterValues
ActiveSheet.Range("$A$1:$AM" & lastRow).AutoFilter Field:=14, Criteria1:=chartbuilder.Sheets("ELN").Range("C5")


Set copyRange = ActiveSheet.Range("J2:J" & lastRow)
copyRange.SpecialCells(xlCellTypeVisible).Copy


chartbuilder.Activate
Set pdttype = Worksheets("ELN")
copyRange.SpecialCells(xlCellTypeVisible).Copy pdttype.Range("C11")


End Sub

This allows me to copy the specific value from the source worksheet (csv) and paste it onto a specific cell in a different worksheet (chartbuilder). However, I realise that I cannot paste special if I use this method. I want to preserve the destination workbook's formatting. How do I edit the code to proceed from here?

 

[frank_AL]

laksham, try changing your last line of code to this:

copyRange.SpecialCells(xlCellTypeVisible).Copy Destination:= pdttype.Range("C11").PasteSpecial(xlPasteValues)