I am trying to take a named range in my sheet and refer to it in VBA in order to query its rows to use in Array(Extracted Rows Here). Thanks so much in advance for the help if anyone thinks of a way.
I am trying to take a named range in my sheet and refer to it in VBA in order to query its rows to use in Array(Extracted Rows Here). Thanks so much in advance for the help if anyone thinks of a way.
Hi chickpeazy,
Not sure if this is exactly what you need but it is a starting point... it will load an array with the row numbers of the named range, just change the sheet name, and the name of the named range.
![]()
Please Login or Register to view this content.
Please help by:
Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know
There are 10 kinds of people in this world... those who understand binary, and those who don't.
Thank you for that! I think you understand what I am trying to do, but implementing it in code that another user here helped make does not do anything for some reason.
Also, thinking about this... an easier way to accomplish what I am trying to do might be to change the color of all cells in the named range because the named range just consists of rows anyway?
PHP Code:
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim varMyRows As Variant
Dim varMyRow As Variant
Dim colFilNorm As Long
Dim colHighlight As Long
Dim rowsForArray As Long
Dim myRow As Range
Dim myArray() As Integer
Dim i As Long
For Each myRow In Worksheets("Sheet1").Range("corValTest").Rows
ReDim Preserve myArray(i)
myArray(i) = myRow.Row
i = i + 1
Next myRow
colFilNorm = ActiveCell.Interior.Color
colHighlight = RGB(255, 255, 0)
varMyRows = Array(myArray) '<- Row(s) to be coloured upon cell B2 being activated
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
For Each varMyRow In varMyRows
Rows(varMyRow).Interior.Color = ActiveCell.Interior.Color
Next varMyRow
If Target.Address = "$B$2" Then
For Each varMyRow In varMyRows
Rows(varMyRow).Interior.Color = colHighlight
Next varMyRow
End If
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub
Last edited by chickpeazy; 05-31-2017 at 11:54 AM.
Why do you have this, myArray is already an array?
Also, if you wanted to format the range you wouldn't need an array or a loop.![]()
Please Login or Register to view this content.
![]()
Please Login or Register to view this content.
If posting code please use code tags, see here.
Ah, yes that command seems to be much more succinct... thank you! Though this code still doesnt do what I want (to highlight the named range cells when "B2" is selected and unhighlight otherwise)
![]()
Please Login or Register to view this content.
Last edited by chickpeazy; 05-31-2017 at 03:26 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks