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

[TechTank]

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:

'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:

    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:

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.

 

[VoG]

Try

Dim MyRows1 As String
MyRows1 = "5:17"

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

Rows(MyRows1).Delete

 

[Trebor76]

Hi Mark,

You were almost there, i.e.

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]

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]

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]

You are welcome. You could also do something like this

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

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

Rows(MyRows1).Delete