Stating an area in vba

[piotr-unia]

I would like to set an area in spreadsheet e.g. from A1 to maximum
value within the same column.
How to express in vba the following operation? Please help me, I
didn.t find it in my vba manual.

 

[Guest]

Try thid:-

Sub sonic()
Set Myrange = Range(Range("A1"), Range("A65536").End(xlUp))
Myrange.Select
End Sub

Mike

 

[piotr-unia]

Sorry, sorry, sorry

Should be "from A1 to maximum value within the same column and then I
want to extend marked area to some columns on the right"

Thanks in advance
Piotr

 

[Guest]

hi,

How do you decide how many columns to add? This resizes by 7 columns.
Sub sonic()
Set myrange = Range(Range("A1"), Range("A65536").End(xlUp))
myrange.Select
Selection.Resize(Selection.Rows.Count + 0, Selection.Columns.Count +
7).Select
End Sub

 

[Mike Fogleman]

This will find the first occurrence of MAX in column A and resize the area
by 4 more columns. Change the +4 as needed:

Sub test()
Dim MyRng As Range
Dim MyMax As Double
Dim LRow As Long

LRow = Cells(Rows.Count, 1).End(xlUp).Row
Set MyRng = Range("A1:A" & LRow)
MyMax = Application.WorksheetFunction.Max(MyRng)
LRow = Cells.Find(MyMax).Row
Set MyRng = Range("A1:A" & LRow)
Set MyRng = MyRng.Resize(MyRng.Rows.Count, MyRng.Columns.Count + 4)
MyRng.Select ,select is optional
End Sub

Mike F