Select Rows where Column value is between 2 markers

[shropslad]

Dear All,

I'm new to VBA and have found this site fantastic for learning and improving coding. That said I'm really struggling with a macro to select rows of data.

I have a spread sheet that is made up of product sub parts. At the beginning and end of each sub part is a sub total line. I need a macro that will select the sub part that the mouse has clicked on.

The a column of a sheet looks a bit like this:
x...A
1...Line Item
2...Sub Total
3...Line Item
4...Line Item
5...Line Item
6...Sub Total

If the cell a4 was selected I would need a macro that could select rows 3, 4, 5, 6. There will not always be the same number of line items in a part. Any Ideas!?

If anyone needs more info or a model sheet then please let me know and I'll get it up.

Many thanks for any help and advice offered!

 

[SydneyGeek]

Welcome to the Board!

Here is code in 2 parts. This goes into a standard module (Alt+F11, Insert>Module, Paste)

Sub GrabSection(lngStartRow As Long, lngStartCol As Long)
    Dim lngTop As Long, _
        lngBottom As Long, _
        lngCurrent As Long
    
    'find the top row of the section
    lngCurrent = lngStartRow
    Do Until InStr(1, Cells(lngCurrent, lngStartCol).Formula, "SUM") > 0
        If lngCurrent = 1 Then
            Exit Do
        Else
            lngCurrent = lngCurrent - 1
        End If
    Loop
    If lngCurrent = 1 Then
        lngTop = lngCurrent
    Else
        lngTop = lngCurrent + 1
    End If
    
    'find the bottom row of the section
    lngCurrent = lngStartRow
    Do Until InStr(1, Cells(lngCurrent, lngStartCol).Formula, "SUM") > 0
        lngCurrent = lngCurrent + 1
    Loop
    lngBottom = lngCurrent
    
    Range(Cells(lngTop, lngStartCol), Cells(lngBottom, lngStartCol)).Select
End Sub

This goes into the module for the worksheet (right-click the sheet tab, View Code, Paste)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If IsEmpty(Target) Then Exit Sub
    
    Call GrabSection(Target.Row, Target.Column)
End Sub

Denis

 

[shropslad]

Dennis, Thankyou very much for the quick reply. I will let you know how I get on.

If I wanted this to do the same for groups between 2 text markers would I remove the .formula in line 7? and replace "sum" with the text?

Also is there a way to make this work no matter what column is selected? e.g. d4

Thanks again

 

[SydneyGeek]

You're welcome.

If I wanted this to do the same for groups between 2 text markers would I remove the .formula in line 7? and replace "sum" with the text?

You can still use .Formula (or .Value if you like, for straight text). And yes, just replace "SUM" with the desired text.

Also is there a way to make this work no matter what column is selected? e.g. d4

The code should already do that. It is designed to work in whatever column you make your selection, but it will not run if you select more than one cell.

Denis

 

[shropslad]

Denis,

Working great.

One last thing - is there a way to get this to run on command as opposed to constantly selecting each part?

So they select a cell, click a button and this runs?

Thanks again