User Defined Print Area Macro

[Curmit]

After several unsuccessful in depth searches looking for the info required to complete the macro I am trying to write I have decided to try and post up my mission in hopes that someone with a lot more knowledge can help me out.

I am looking to create a macro that will ask the user to define the row number of the beginning and ending locations that they would like to print. The columns are fixed in that the first starting row will be from column A and the ending row will be column M.

I have built a pipe tally used in the oilfield that keeps track of large amounts of pipe figures but very often is the case that there is the need to print a section of the tally data in reference to a certain depth. With the vast knowledge base located here I was able to build the first macro that prints the whole active tally but now I need something more refined.

Below is one of the copies of the macro that I was trying to get to work and it is missing a couple of things but the most important part shows up and that is how to get the inputbox variables into the defined print area.

Sub TallyVariable()
Dim StartRow As String
Dim EndRow As String
StartRow = InputBox("Please Enter Starting Row you would like to print")
EndRow = InputBox("Please Enter Last Row you would like to print")
ActiveSheet.PageSetup.PrintArea = "A" & StartRow.Address:"M" & EndRow.Address
End Sub

Thanks in advance to anyone who might be able to help, I just got into using excel and most things I manage to figure out but this one is over my head.

 

[Norie]

Remove Address here.

ActiveSheet.PageSetup.PrintArea = "A" & StartRow.Address:"M" & EndRow.Address

 

[VoG]

It should be easier to allow the user to select the print area:

Sub PAreas()
Dim r As Range
Set r = Application.InputBox("Select print area", Type:=8)
If r Is Nothing Then Exit Sub
ActiveSheet.PageSetup.PrintArea = r.Address
End Sub

They can either enter an area or select it using the mouse.

 

[Curmit]

Remove Address here.

ActiveSheet.PageSetup.PrintArea = "A" & StartRow.Address:"M" & EndRow.Address

Not sure I understand, I tried to remove the address like you said but it gives up an error each time.

 

[Curmit]

It should be easier to allow the user to select the print area:

Sub PAreas()
Dim r As Range
Set r = Application.InputBox("Select print area", Type:=8)
If r Is Nothing Then Exit Sub
ActiveSheet.PageSetup.PrintArea = r.Address
End Sub

They can either enter an area or select it using the mouse.

It might seem easier but this has to be set up for people who do not know how to use a computer, having to enter a print range into a box would have to be explained in fine detail versus my approach of which row to start printing at and which row to end printing at.

 

[VoG]

It might seem easier but this has to be set up for people who do not know how to use a computer, having to enter a print range into a box would have to be explained in fine detail versus my approach of which row to start printing at and which row to end printing at.

Can they not use a mouse to select a range of cells?

 

[Curmit]

The data being selected can range from 10 lines to several hundred which would span several pages.

 

[VoG]

Try this

Sub TallyVariable()
Dim StartRow As String
Dim EndRow As String
StartRow = InputBox("Please Enter Starting Row you would like to print")
EndRow = InputBox("Please Enter Last Row you would like to print")
If StartRow = "" Or EndRow = "" Then Exit Sub
ActiveSheet.PageSetup.PrintArea = Range("A" & StartRow & ":M" & EndRow).Address
End Sub

 

[VoG]

Actually, this might be better (only one InputBox)

Sub TallyVariable()
Dim UserInput As String, X
UserInput = InputBox("Please enter Start and End rows that you would like to print, separated by a space e.g. 4 56")
If UserInput = "" Then Exit Sub
X = Split(UserInput)
If IsArray(X) Then
    ActiveSheet.PageSetup.PrintArea = Range("A" & X(LBound(X)) & ":M" & X(UBound(X))).Address
End If
End Sub

 

[Evagrius Ponticus]

VogII,

why does LBound(X) = 0 and UBound(X) = 1?

Does the split function separate 4 56 using " " as a dilineator?

Thanks for any explanation.