select a range using inputbox method

Question

Hi,

How can I select a range in worksheet by VBA which define by

• The first cell is input through inputbox method
• The last cell in the cell in last row and last column

For example: worksheet have data in Cell F4, H4 and K9 as follows

F4		H4
					K9

So when I run the code, it firstly open the input box so that I can enter cell "F4", then It allows to select range (F4:K9)

Thks

Answer 1

Hi,

Thanks for picking up on the typing error (TheRwo)

XLToLeft was correct and the change you have made is making the code select all the columns. Try it like this

Sub Select_Range()

Dim lastrow As Long, LastCol As Long

Dim TheRow As Long, TheCol As Long

Dim StartCell As String

LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

lastrow = Cells(Rows.Count, LastCol).End(xlUp).Row

StartCell = InputBox("Enter Start cell")

TheRow = Range(StartCell).Row

TheCol = Range(StartCell).Column

ActiveSheet.Range(Cells(TheRow, TheCol), Cells(lastrow, LastCol)).Select

End Sub

Answer 2

Hi,

Thanks for picking up on the typing error (TheRwo)

XLToLeft was correct and the change you have made is making the code select all the columns. Try it like this

Sub Select_Range()

Dim lastrow As Long, LastCol As Long

Dim TheRow As Long, TheCol As Long

Dim StartCell As String

LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

lastrow = Cells(Rows.Count, LastCol).End(xlUp).Row

StartCell = InputBox("Enter Start cell")

TheRow = Range(StartCell).Row

TheCol = Range(StartCell).Column

ActiveSheet.Range(Cells(TheRow, TheCol), Cells(lastrow, LastCol)).Select

End Sub

Maybe I have some mistake to test the code. It works well now

Thks for your help

Answer 3

Hi,

Thanks for picking up on the typing error (TheRwo)

XLToLeft was correct and the change you have made is making the code select all the columns. Try it like this

Sub Select_Range()

Dim lastrow As Long, LastCol As Long

Dim TheRow As Long, TheCol As Long

Dim StartCell As String

LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

lastrow = Cells(Rows.Count, LastCol).End(xlUp).Row

StartCell = InputBox("Enter Start cell")

TheRow = Range(StartCell).Row

TheCol = Range(StartCell).Column

ActiveSheet.Range(Cells(TheRow, TheCol), Cells(lastrow, LastCol)).Select

End Sub

The code works well, but it is not what I really expect, since :

• I need to input the last cell (instead of the first cell) for the start cell in Input box 
• It does not exclude empty cell from the left of the start cell

Let me give another example to make clear what I want. Suppose my worksheet look like

	B1 <br><br>(Title)	C1 <br><br>(Title)	D1 <br><br>(Title)
	B2 <br><br>( Data)
	B3 <br><br>( Data)
			D1000 <br><br>(Data)

Then, I need to select range (B2 : D1000), excluded Title area and column from the start cell to the left

If using your code:

•  I need to go down for the Cell D1000 to input cell reference in Input box
•  The selected range does not exclude (A1:A1000)

What I expect:

• Input B2 in input box
• Code automatically look for D1000 (define by the last cell in the last column with data from the right of B2)
• The selected range will be defined by diagonal from B2 to D1000 , exclude A1:A1000 and Title Area (B1:D1)

Is that possible. Pls help

Answer 4

Hi,

Whatever it is you're trying to do can almost certainly be done without selecting anything but this does what you want.

Sub Select_Range()

Dim lastrow As Long, LastCol As Long

Dim TheRwo As Long, TheCol As Long

Dim StartCell As String

LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

lastrow = Cells(Rows.Count, LastCol).End(xlUp).Row

StartCell = InputBox("Enter Start cell")

TheRow = Range(StartCell).Row

TheCol = Range(StartCell).Column

ActiveSheet.Range(Cells(TheRow, TheCol), Cells(lastrow, LastCol)).Select

End Sub

Thks for your response

I try your code with example in my first code ( after correct spelling error in "Dim TheRwo" - actually "Dim TheRow, and correct XlToLeft to XlToRight in "LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

But it does not work as I expect.  It work as follows

• It select all the row from 1 to 4, and start from Cell F4 till the last column in worksheet (despite the columns contain data or not)
• It ignore cell K9 (K9 is out of range)

Pls give me more instruction

Answer 5

Hi,

Whatever it is you're trying to do can almost certainly be done without selecting anything but this does what you want.

Sub Select_Range()

Dim lastrow As Long, LastCol As Long

Dim TheRwo As Long, TheCol As Long

Dim StartCell As String

LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

lastrow = Cells(Rows.Count, LastCol).End(xlUp).Row

StartCell = InputBox("Enter Start cell")

TheRow = Range(StartCell).Row

TheCol = Range(StartCell).Column

ActiveSheet.Range(Cells(TheRow, TheCol), Cells(lastrow, LastCol)).Select

End Sub