Macro to select a variable range

[Elmtree]

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

10 times (out to the N Column).

Is this even possible?

Thanks for any assistance.

Mike​

 

[JLGWhiz]

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

 

[JLGWhiz]

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
myRange = (Range"D1:N" & DlstRw)
MsgBox myRange.Address
End Sub

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

10 times (out to the N Column).

Is this even possible?

Thanks for any assistance.

Mike
​

​

​

 

[Elmtree]

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


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

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]

 

[Per Jessen]

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

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

---------------------------------------------------------------------------­---
----------------

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 -

 

[JLGWhiz]

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

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


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

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

 

[Elmtree]

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.

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]

 

[Elmtree]

Set myRange = Range("D1:N" & DlstRow)

I do not start at Row 1.


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.

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

[quoted text clipped - 10 lines]

 

[Elmtree]

I've made some changes, however 1 thing eludes me, The starting Row.For my
example I assume starting on row 29.

-------

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

Range(myRange.Address).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With

Set myRange = Range("D29" & DlstRow) 'Get current range

Range(myRange.Address).Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With

-------

This macro selects my range, colors it yelow, then goes to column D and
unhighlights it. All works like I need it to, but the starting row is my
problem. I can live with starting on row 29. (For now!)

thanks for your assistance!!!!!


Mike

Set myRange = Range("D1:N" & DlstRow)

I do not start at Row 1.

Mike

OK, let me throw a curve:

[quoted text clipped - 7 lines]

 

[JLGWhiz]

I see you worked the starting row out. This will color the
entire range yellow. It is difficult to solve the starting row problem
because the information you have provided to describe the sheet
contents is a little ambiguous. Howeve, if you have no data on the
except in columns D through N, then it is possible to define the
starting row with the UsedRange property. Then you could use
the code below to get the range and color it yellow.

Dim DlstRow As Long, Rw1st As Long
Rw1st = ActiveSheet.UsedRange.Row
DlstRow = Cells(Rows.Count, 4).End(xlUp).Row
Set myRange = Range("D" & Rw1st & ":N" & DlstRow)
cRng = myRange.Address
Range(cRng).Interior.ColorIndex = 6
End With

However, If you have any data in any row above the range you want to color
code, then the code above will fail for your purposes. But, if you want to
think about it a while and start a new thread with a good descriprion of what
you are working with, and what you are trying to do, someone will help you to
do it.

I've made some changes, however 1 thing eludes me, The starting Row.For my
example I assume starting on row 29.

-------

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

Range(myRange.Address).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With

Set myRange = Range("D29" & DlstRow) 'Get current range

Range(myRange.Address).Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With

-------

This macro selects my range, colors it yelow, then goes to column D and
unhighlights it. All works like I need it to, but the starting row is my
problem. I can live with starting on row 29. (For now!)

thanks for your assistance!!!!!


Mike

Set myRange = Range("D1:N" & DlstRow)

I do not start at Row 1.

Mike

OK, let me throw a curve:

[quoted text clipped - 7 lines]

Mike