Variable Help - XL 2003 VBA - Declaring and Using a variable

TechTank

Board Regular
Joined
Sep 5, 2011
Messages
92
Hi,

Forgive me as this will probably be so simple for most of you but my searches haven't returned anything that I can use and my knowledge is limited to an extent in VBA.

I have several lines of code that require certain rows numbers to be used:

Code:
'Copys The Row Specified
                Sheets("This IS Sheet 1").Rows("5:17").Copy
This is used in a macro that copies certain specific rows from one sheet to another (Rows("5:17")) in the above example.

These rows numbers change and I'd like to declare a variable at the start of the macro to make amending easier rather than scrolling dwon through the code looking for them.

The second thing I use is another code string to remove the rows within the same macro (it's along winded explanation that I won't go into) and this looks like:

Code:
    Rows("5:17").Delete
If anyone knows how I can declare at the beginning the variable and value to replace the 5:17 it would be much appreciate. I am envisioning something like this:

Code:
Dim MyRows1 as variable
MyRows1 = "5:17"

Sheets("Debt Remedy Template Steps").Rows("MyRows1").Copy

Rows("MyRows1").Delete
Any help greatly appreciated and thank you for your time.

Mark.
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
Try

Code:
Dim MyRows1 As String
MyRows1 = "5:17"

Sheets("Debt Remedy Template Steps").Rows(MyRows1).Copy

Rows(MyRows1).Delete
 

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,587
Hi Mark,

You were almost there, i.e.

Code:
Option Explicit
Sub Macro1()

    Dim strMyRows As String
    
    strMyRows = "5:17"
    
    Sheets("Debt Remedy Template Steps").Rows(strMyRows).Copy

    Rows(strMyRows).Delete

End Sub
HTH

Robert
 

TechTank

Board Regular
Joined
Sep 5, 2011
Messages
92
Ha ha would you believe it...not as stupid as I look by pure accident :)

Thanks for the quick responses, I'll give it a go.
 

TechTank

Board Regular
Joined
Sep 5, 2011
Messages
92
:) Both solutions are perfect. :)

Thank you again this has saved me a lot of scrolling time and makes my code so much easier to maintain.

Mark.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
You are welcome. You could also do something like this

Code:
Dim MyRows1 As String
MyRows1 = InputBox("Enter rows", "Row numbers", "5:17")

Sheets("Debt Remedy Template Steps").Rows(MyRows1).Copy

Rows(MyRows1).Delete
 

Watch MrExcel Video

Forum statistics

Threads
1,095,939
Messages
5,447,422
Members
405,451
Latest member
BalbasNiBarabas

This Week's Hot Topics

Top