PDA

View Full Version : Selecting dynamic range in Excel VBA



Jurij
03-30-2015, 08:39 AM
Hi guys, I have a quick question.

Take this example for instance:



Range("A1:B"&Range("A1").End.Row


This gives you a dynamic range, attaching the row number of the last filled row in column A to "B".

I want to do a similar dynamic thing, but I want to do it on both sides of the range (something like the code below, but it does not work, obviously:



Range("A"&Range("Named range").Offset(1,0).Row:"D"&Range("Named range").Offset(1,0).Row)


Basically, I want to select a Range that is based on the position of a named cell (the "Named range"). If the cell is A20, I want to select A20:D20. If it is 25, I want to select A25:D25,

I am sure there is a quick solution for this, but I can not think of it at all.

Any input is appreciated,

Thanks,

Jurij

Yongle
03-30-2015, 09:19 AM
Smileys in the middle of the question are most confusing.:confused::confused:

If the cell is A20
- which is the first cell
- which is the last cell
of the selected range?

If the cell is A25
- which is the first cell
- which is the last cell
of the selected range?

Yongle
03-30-2015, 09:20 AM
Smileys in the middle of the question are most confusing.:confused::confused:

If the cell is A20
- which is the first cell
- which is the last cell
of the selected range?

If the cell is A25
- which is the first cell
- which is the last cell
of the selected range?

Jurij
03-30-2015, 01:14 PM
Smileys in the middle of the question are most confusing.:confused::confused:

If the cell is A20
- which is the first cell
- which is the last cell
of the selected range?

If the cell is A25
- which is the first cell
- which is the last cell
of the selected range?

Apologies for the smileys, they were not intended.

If the cell is A20 the first cell of the range should be A21 (done with offset property) and the last D21.

If the cell is A25 the first should be A25 and the last D26.

Appreciate the help,

Jurij

Yongle
03-31-2015, 12:48 AM
Here is vba to capture the named cell and create a dynamic range.
This assumes that there are only 2 possible values for the named cell A20 and A25 as per your comments.
I have included some message boxes to illustrate how you can build a few checks into the vba to help see what is going on.
What you were looking for was to combine Offset with Resize.
Offset moves the first cell of a range whereas Resize stretches the range.



Sub SelectRange()'declare variables
Dim NamedCell As Range, MyRange As Range, InputValue As String
'capture input
InputValue = InputBox(Prompt:="A20 or A25", Title:="CHOOSE NAMED CELL", Default:="a20")
InputValue = UCase(InputValue) 'ensures input is upper case
Set NamedCell = Range(InputValue)
'based on input, determine range
If InputValue = "A20" Then
Set MyRange = NamedCell.Offset(1, 0).Resize(, 4)
MsgBox "Named Cell = " & NamedCell.Address(0, 0) & vbNewLine & "MyRange is : " & MyRange.Address(0, 0)
Else
If InputValue = "A25" Then
Set MyRange = NamedCell.Resize(, 4)
MsgBox "Named Cell = " & NamedCell.Address(0, 0) & vbNewLine & "MyRange is : " & MyRange.Address(0, 0)
Else
MsgBox "Valid Named Cell values:" & vbNewLine & "Either A20 OR A25"
End If
End If


End Sub