How to get the Range of Cells with "Values" within a Range of formulas

[jrtraylor]

I have a range of formulas (E12:O21) The Header Row is (E11-O11) - Total Row is (E21:O21).
I want to use VBA to search that range of formulas and find the cells with values to return the "actual" used range within that range. I will use this new range to create a chart eliminating blank chart areas. The formulas in the range will always display values in order meaning they will fill down and across and will not have values scattered around the formula range. The below example would return a new Range of (E12:I16).

	E	F	G	H	I	J	K	L	M	N
11	Group 1	Group 2	Group 3	Group 4	Group 5	Group 6	Group 7	Group 8	Group 9	Group 10
12	V	V	V	V	V
13	V	V		V	V
14		V		V	V
15		V		V
16				V
17
18
19
20
21

 

[jrtraylor]

I did try to look at each cell to create an array of the cell addresses that contain the values but once i had that, I could not figure out how to convert the array of cells back to a range.

Sub RangeTest()
Dim grpRng As Range, NewgrpRng As Range
Dim ws1 As Worksheet
Dim x As Variant


Set ws1 = ThisWorkbook.Sheets("Affinity (3)")


Set grpRng = ws1.Range("E12:K19")




For Each cel In grpRng
    If cel.Value > "" Then
      x = x + cel.Address
    End If
Next cel


 MsgBox x
End Sub

 

[Artik]

If by the method of looping over all the cells of the range, then rather this way:

Sub AAA()
    Dim grpRng As Range
    Dim cel As Range
    Dim ws1 As Worksheet
    Dim lColStart As Long, lColEnd As Long
    Dim lRowStart As Long, lRowEnd As Long
    Dim rngResult As Range

    Set ws1 = ActiveSheet

    Set grpRng = ws1.Range("E12:O20")

    lRowEnd = grpRng.Row
    lColEnd = grpRng.Column
    lRowStart = grpRng.Row
    lColStart = grpRng.Column

    For Each cel In grpRng
        If cel.Value <> "" Then
            If cel.Row < lRowStart Then
                lRowStart = cel.Row
            End If
            If cel.Row > lRowEnd Then
                lRowEnd = cel.Row
            End If
            If cel.Column < lColStart Then
                lColStart = cel.Column
            End If
            If cel.Column > lColEnd Then
                lColEnd = cel.Column
            End If
        End If
    Next cel

    Set rngResult = ws1.Range(ws1.Cells(lRowStart, lColStart), ws1.Cells(lRowEnd, lColEnd))
    MsgBox rngResult.Address(0, 0)

End Sub

Artik

 

[Rick Rothstein]

Give this a try...

Sub SetUsedRange()

    Dim LastRow As Long,  LastCol As Long
    Dim UsedRange As Range

    LastRow = Range("E12:N21").Find("V", , xlValues, , xlRows, xlPrevious).Row
    LastCol = Range("E12:N21").Find("V", , xlValues, , xlByColumns, xlPrevious).Column
    Set UsedRange = Range("E12", Cells(LastRow, LastCol))

    MsgBox UsedRange.Address(0, 0)

End Sub

 

[jrtraylor]

If by the method of looping over all the cells of the range, then rather this way:

Sub AAA()
    Dim grpRng As Range
    Dim cel As Range
    Dim ws1 As Worksheet
    Dim lColStart As Long, lColEnd As Long
    Dim lRowStart As Long, lRowEnd As Long
    Dim rngResult As Range

    Set ws1 = ActiveSheet

    Set grpRng = ws1.Range("E12:O20")

    lRowEnd = grpRng.Row
    lColEnd = grpRng.Column
    lRowStart = grpRng.Row
    lColStart = grpRng.Column

    For Each cel In grpRng
        If cel.Value <> "" Then
            If cel.Row < lRowStart Then
                lRowStart = cel.Row
            End If
            If cel.Row > lRowEnd Then
                lRowEnd = cel.Row
            End If
            If cel.Column < lColStart Then
                lColStart = cel.Column
            End If
            If cel.Column > lColEnd Then
                lColEnd = cel.Column
            End If
        End If
    Next cel

    Set rngResult = ws1.Range(ws1.Cells(lRowStart, lColStart), ws1.Cells(lRowEnd, lColEnd))
    MsgBox rngResult.Address(0, 0)

End Sub

Give this a try...

Sub SetUsedRange()

    Dim LastRow As Long,  LastCol As Long
    Dim UsedRange As Range

    LastRow = Range("E12:N21").Find("V", , xlValues, , xlRows, xlPrevious).Row
    LastCol = Range("E12:N21").Find("V", , xlValues, , xlByColumns, xlPrevious).Column
    Set UsedRange = Range("E12", Cells(LastRow, LastCol))

    MsgBox UsedRange.Address(0, 0)

End Sub

Thank you to both of you. Both solutions work perfectly. I am going to have to work with the Find method a little more. I had seen some other posts that mentioned using it to find "Values" but could not figure out how to get it to work for me so turned to the for each cell option.