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.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Try

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

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

Rows(MyRows1).Delete
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
:) 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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,203,242
Messages
6,054,354
Members
444,718
Latest member
r0nster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top