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.

 

[VoG]

The Split function can take several arguments of which only two are normally used

=Split(Text, Separator)

Text is the text to be split and Separator is the delimiter. By default the delimiter is a space and in that case does not need to be specified (as in the code that I posted).

Split generates a one-dimensional array. The lower element number is dictated by the settings that you have set using Option Base X. You clearly have not used that option so the default applies - the lowest member of the array is element 0. If you had used at the top of your module

Option Base 1

then the first element of X would be X(1). By using LBound and Ubound in my code I ensure that it will work whatever convention the user has chosen for array numbering.

I hope that this makes sense

 

[Evagrius Ponticus]

Thanks VogII - great and thorough explanation.

I had to read your post a couple of times but I believe I understand. Here goes.

You use the Split function to separate the Array elements entered in the inputbox.

You employed the Ubound/Lbound to isolate the relative place of the element in the array. So if the array convention isn't 0, 1 , 2 etc, it wouldn't matter since Lbound would be 0 and ubound would be 2. Right? My verbage may be a tad off since I am not fully familiar with proper programming language

 

[VoG]

Yes, you have understood exactly. Well done

Thanks VogII - great and thorough explanation.

I had to read your post a couple of times but I believe I understand. Here goes.

You use the Split function to separate the Array elements entered in the inputbox.

You employed the Ubound/Lbound to isolate the relative place of the element in the array. So if the array convention isn't 0, 1 , 2 etc, it wouldn't matter since Lbound would be 0 and ubound would be 2. Right? My verbage may be a tad off since I am not fully familiar with proper programming language

 

[Evagrius Ponticus]

- Thanks VogII

 

[Curmit]

Awesome, thanks for the help VogII that was excactly what I was looking for. I had forgot to put one line of code in that macro to pop up the print dialog box, the final code is below for anyone else who might have a need for something like 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
Application.Dialogs(xlDialogPrint).Show
End Sub