CODEWITHSUNDEEP
excelvbaexcel-formula
Sandeep Bhatt
Sandeep Bhatt

Reputation: 537

How to store row number in a variable in Worksheet_SelectionChange event in VBA?

I am using a Worksheet_SelectionChange event in which I have to get a current row number and use it for a particular event.

I have to get a row number and store it in a variable so that I can use it for a specific event.

I have a drop-down list ("Yes/No", "True/False", "Single", "Multiple") in a column "C".

• If user selects "Yes/No", then user need to be select one "True" option in column "D".

• If user selects "Multiple" in column "C", then user needs to select at least 2 "True" option in column "D" and so on for other selection...

• If the user didn't select the appropriate option in column "D", it will pop up a warning message until the user will not select appropriate options.

Here is my code:

I am achieving the results what I have mentioned above but my code is static every time I have to mention a row number. 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    rw = 10
    rng1 = rw + 1
    rng2 = rw + 4

    If Worksheets("Question paper").Range("D" & rw).Value = "Multiple Choice/ Multiple Answer" And _
       WorksheetFunction.CountIf(Range("E" & rng1 & ":" & "E" & rng2), "TRUE") < 2 Then

        MsgBox "Select at least 2 ""TRUE"" option"

    ' for Multiple Choice/Single Answer
    ' if user select more than 1 answer
    ElseIf Worksheets("Question paper").Range("D" & rw).Value = "Multiple Choice/Single Answer" And _
           WorksheetFunction.CountIf(Range("E" & rng1 & ":" & "E" & rng2), "TRUE") > 1 Then

        MsgBox "Select only 1 ""TRUE"" option" 

    End If

I am expecting a dynamic solution so I can use it across the sheet. No need to assign a variable with a row number for a particular range.

Upvotes: 0

Views: 2037

Answers (1)

Aman
Aman

Reputation: 304

If you need only row number, this might help:

currentRowNumber = Range("A1").Row

The code above should assign row number to a variable. Instead of Range("A1") you can put anything, like:

currentRowNumber = Target.Row

Upvotes: 1

Related Questions