CODEWITHSUNDEEP
excelvba
prestan
prestan

Reputation: 63

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

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}"

Upvotes: 0

Views: 5195

Answers (1)

Gowtham Shiva
Gowtham Shiva

Reputation: 3875

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.

Upvotes: 1

Related Questions