How to select range using inputbox from start cell and end cell using VBA excel

Question

I need to select a range that starts from, for example C5 and end at P13.

I did some coding, but there's something wrong with it, I can't figure it out. Its doesn't merged in. Any suggestions?

Sub Select_Range()
Dim lastrow As Long, LastCol As Long
Dim TheRow As Long, TheCol As Long
Dim StartCell As String
Dim EndCell As String
Dim startrow As Long, StartCol As Long
Dim TheRow2 As Long, TheCol2 As Long

StartCol = ActiveSheet.Cells(1, Columns.Count).End(xlToRight).Column
startrow = Cells(Rows.Count, StartCol).End(xlDown).Row
StartCell = InputBox("Enter Start cell")
TheRow2 = Range(StartCell).Row
TheCol2 = Range(StartCell).Column
ActiveSheet.Range(Cells(TheRow2, TheCol2), Cells(startrow, StartCol)).Select

LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
lastrow = Cells(Rows.Count, LastCol).End(xlUp).Row
EndCell = InputBox("Enter End cell")
TheRow = Range(EndCell).Row
TheCol = Range(EndCell).Column

ActiveSheet.Range(Cells(TheRow2, TheCol2), Cells(startrow, StartCol)).Select
ActiveSheet.Range(Cells(TheRow, TheCol), Cells(lastrow, LastCol)).Select

End Sub

After I get to select the range I want, I need to insert formula to the selected range. And how do I manipulate the formula to become the selected range? This is my formula:

ActiveCell.range.Formula = "{=MAX(($C$3:$S$20=D27)*COLUMN($C$3:$S$20))-COLUMN($C$3:$S$20)+1}"

There's a lot going on in there that I'm concerned may not work as you want. Does your data *not* start in `A1` to say `F1`? Because as you have it, it's possible that your `StartCol` and `EndCol` are the same. What does your data look like? Also, you should [avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251). — BruceWayne, Jul 27 '17 at 02:50
No. The data is not specifically start and end at the same cell all the time, that's why I created an inputbox to do it. When I enter Start cell, it does start from the cell I input, but it doesn't end at the cell that I want. — prestan, Jul 27 '17 at 03:17

score 1 · Answer 1 · answered Jul 27 '17 at 15:39

This might help you,

Sub PromptRangeSelection()
    Dim rng As Range
    Set rng = Application.InputBox("Select a range", "Title Here", Type:=8)
    MsgBox "The Range is " & rng.Address
End Sub

You can directly select a range by selecting and dragging the mouse, or typing the range in the input box.

How to select range using inputbox from start cell and end cell using VBA excel

1 Answers1