I'm trying to create a macro that will select a variable range. By variable
range I mean a range that has a set amount of colums, but the rows grow each
month.

My data: I have columns A - N, but the rows grow each month.

What I'm specifically trying to do is create a macro that move over to the D
column Select [END] [DOWN} which would select from the top to the bottom of
my data set, then [right] 10 times (out to the N Column).

Is this even possible?

Thanks for any assistance.

Mike

Sub selDlstRw()
DlstRow = Cells(Rows.Count, 4).End(xlUp).Row
myRange = (Range"D1:N" & DlstRw)
MsgBox myRange.Address
End Sub

"Elmtree" wrote:

I'm trying to create a macro that will select a variable range. By variable
range I mean a range that has a set amount of colums, but the rows grow each
month.

My data: I have columns A - N, but the rows grow each month.

What I'm specifically trying to do is create a macro that move over to the D
column Select [END] [DOWN} which would select from the top to the bottom of
my data set, then [right] 10 times (out to the N Column).

Is this even possible?

Thanks for any assistance.

Mike

Correct the typo:

Sub selDlstRw()
DlstRow = Cells(Rows.Count, 4).End(xlUp).Row 'Get last data cell in
Col. D
myRange = Range("D1:N" & DlstRw) 'Get current range
MsgBox myRange.Address 'Display current range address
End Sub


"JLGWhiz" wrote:

Sub selDlstRw()
DlstRow = Cells(Rows.Count, 4).End(xlUp).Row
myRange = (Range"D1:N" & DlstRw)
MsgBox myRange.Address
End Sub

"Elmtree" wrote:

I'm trying to create a macro that will select a variable range. By variable
range I mean a range that has a set amount of colums, but the rows grow each
month.

My data: I have columns A - N, but the rows grow each month.

What I'm specifically trying to do is create a macro that move over to the D
column Select [END] [DOWN} which would select from the top to the bottom of
my data set, then [right] 10 times (out to the N Column).

Is this even possible?

Thanks for any assistance.

Mike

When I try to run that macro I get

Run Time Error '4004'
Method "Range 'of object'_Global" failed.


When I try to debug, this line is highlighted.


myRange = Range("D1:N" & DlstRw) 'Get current range


Here is the macro as it appears:

-----------------------------------
Sub selDlstRw()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+Shift+M
'


DlstRow = Cells(Rows.Count, 4).End(xlUp).Row 'Get last data cell inCol.D

myRange = Range("D1:N" & DlstRw) 'Get current range

MsgBox myRange.Address 'Display current range address


End Sub

--------------------------------

It's probably a simple solution, but it's Friday and my brain is tired.....



thanks

Mike


------------------------------------------------------------------------------
----------------

JLGWhiz wrote:
Correct the typo:

Sub selDlstRw()
DlstRow = Cells(Rows.Count, 4).End(xlUp).Row 'Get last data cell in
Col. D
myRange = Range("D1:N" & DlstRw) 'Get current range
MsgBox myRange.Address 'Display current range address
End Sub

Sub selDlstRw()
DlstRow = Cells(Rows.Count, 4).End(xlUp).Row
[quoted text clipped - 17 lines]

Mike

Hi Mike

This shold do it:

Sub selDlstRw()
DlstRow = Cells(Rows.Count, 4).End(xlUp).Row 'Get last data cell
in Col.D
Set MyRange = Range("D1:N" & DlstRow) 'Get current range
MsgBox MyRange.Address 'Display current range address
End Sub

Regards,
Per

On 29 Aug., 20:58, "Elmtree" <u45848@uwe wrote:
When I try to run that macro I get

Run Time Error '4004'
Method "Range 'of object'_Global" failed.

When I try to debug, this line is highlighted.

myRange = Range("D1:N" & DlstRw) * 'Get current range

Here is the macro as it appears:

-----------------------------------
Sub selDlstRw()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+Shift+M
'

* DlstRow = Cells(Rows.Count, 4).End(xlUp).Row * 'Get last data cell inCol.D

* myRange = Range("D1:N" & DlstRw) * 'Get current range

* MsgBox myRange.Address * * 'Display current range address

End Sub

--------------------------------

It's probably a simple solution, but it's Friday and my brain is tired......

thanks

Mike

---------------------------------------------------------------------------*---
----------------



JLGWhiz wrote:
Correct the typo:

Sub selDlstRw()
* DlstRow = Cells(Rows.Count, 4).End(xlUp).Row * 'Get last data cell in
Col. D
* myRange = Range("D1:N" & DlstRw) * 'Get current range
* MsgBox myRange.Address * * 'Display current range address
End Sub

Sub selDlstRw()
* *DlstRow = Cells(Rows.Count, 4).End(xlUp).Row
[quoted text clipped - 17 lines]

Mike- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

When I do things in a hurry, I tend to screw up. This tested OK.


Sub selDlstRw()
Dim DlstRow As Long
DlstRow = Cells(Rows.Count, 4).End(xlUp).Row
Set myRange = Range("D1:N" & DlstRow) 'Get current range
MsgBox myRange.Address 'Display current range address
End Sub


"Elmtree" wrote:

When I try to run that macro I get

Run Time Error '4004'
Method "Range 'of object'_Global" failed.


When I try to debug, this line is highlighted.


myRange = Range("D1:N" & DlstRw) 'Get current range


Here is the macro as it appears:

-----------------------------------
Sub selDlstRw()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+Shift+M
'


DlstRow = Cells(Rows.Count, 4).End(xlUp).Row 'Get last data cell inCol.D

myRange = Range("D1:N" & DlstRw) 'Get current range

MsgBox myRange.Address 'Display current range address


End Sub

--------------------------------

It's probably a simple solution, but it's Friday and my brain is tired.....



thanks

Mike


------------------------------------------------------------------------------
----------------

JLGWhiz wrote:
Correct the typo:

Sub selDlstRw()
DlstRow = Cells(Rows.Count, 4).End(xlUp).Row 'Get last data cell in
Col. D
myRange = Range("D1:N" & DlstRw) 'Get current range
MsgBox myRange.Address 'Display current range address
End Sub

Sub selDlstRw()
DlstRow = Cells(Rows.Count, 4).End(xlUp).Row
[quoted text clipped - 17 lines]

Mike

OK, let me throw a curve:

This works, but I forgot the Final Step (I said it's Friday....)

I need to paint this selected range yellow.




JLGWhiz wrote:
When I do things in a hurry, I tend to screw up. This tested OK.

Sub selDlstRw()
Dim DlstRow As Long
DlstRow = Cells(Rows.Count, 4).End(xlUp).Row
Set myRange = Range("D1:N" & DlstRow) 'Get current range
MsgBox myRange.Address 'Display current range address
End Sub

When I try to run that macro I get

[quoted text clipped - 49 lines]

Mike