Macro assistance

[rjc4]

Using macros my spreadsheet extracts data and puts this extracted data in the range BU2:BV...... The extraction range is variable but can be up to 30 rows.

The data is presented like this:-
ColBU ColBV
Ford FS
Honda HR
Renault RR
Chrysler CS
Buick BR

Could someone assist me with a macro that just re-sorts the range, but only includes the data ending in "R's" in col BV.

So in my example above, my end result would be:-
Honda HR
Renault RR
Buick BR

Thanks in advance!

 

[andylawson5]

A little messy, but should work:

Sub sortandFilter()

Dim sh As Worksheet
Dim rawData() As Variant
Dim rowCount, RowIndex, nCount, n As Integer

Set sh = Worksheets("Sheet1")

rowCount = 2
While sh.Range("BU" & rowCount) <> ""
    rowCount = rowCount + 1
Wend

ReDim rawData(rowCount, 2)

RowIndex = 2
nCount = 1

While sh.Range("BU" & RowIndex) <> ""
    
    If Right(sh.Range("BV" & RowIndex), 1) = "R" Then
        rawData(nCount, 1) = sh.Range("BU" & RowIndex)
        rawData(nCount, 2) = sh.Range("BV" & RowIndex)
        nCount = nCount + 1
    End If
    RowIndex = RowIndex + 1
Wend

sh.Range("BU2:BV" & rowCount).ClearContents

For n = 1 To rowCount
    sh.Range("BU" & 1 + n) = rawData(n, 1)
    sh.Range("BV" & 1 + n) = rawData(n, 2)
Next n

End Sub

 

[rjc4]

Thank you Andy for your help!

Cheers,
Rick

 

[rjc4]

Hello Andy,
Have tried the macro and it works fine except for this:-
In column BV I have formulas which provide the answers of FS HR etc. The macro of course deletes these formulas.

Have just re-read my post and I was remiss in not providing this vital bit of info. Is it possible to revise the macro that leaves my formulas in place and just uses the formula result in the calculations.

Sorry for being so thick!
Cheers,
Rick

A little messy, but should work:

Sub sortandFilter()

Dim sh As Worksheet
Dim rawData() As Variant
Dim rowCount, RowIndex, nCount, n As Integer

Set sh = Worksheets("Sheet1")

rowCount = 2
While sh.Range("BU" & rowCount) <> ""
    rowCount = rowCount + 1
Wend

ReDim rawData(rowCount, 2)

RowIndex = 2
nCount = 1

While sh.Range("BU" & RowIndex) <> ""
    
    If Right(sh.Range("BV" & RowIndex), 1) = "R" Then
        rawData(nCount, 1) = sh.Range("BU" & RowIndex)
        rawData(nCount, 2) = sh.Range("BV" & RowIndex)
        nCount = nCount + 1
    End If
    RowIndex = RowIndex + 1
Wend

sh.Range("BU2:BV" & rowCount).ClearContents

For n = 1 To rowCount
    sh.Range("BU" & 1 + n) = rawData(n, 1)
    sh.Range("BV" & 1 + n) = rawData(n, 2)
Next n

End Sub

 

[andylawson5]

Hi Rick,

I've modified the code so it outputs a formula in BV:

Sub sortandFilter()

Dim sh As Worksheet
Dim rawData() As Variant
Dim rowCount, RowIndex, nCount, n As Integer

Set sh = Worksheets("Sheet1")

rowCount = 2
While sh.Range("BU" & rowCount) <> ""
    rowCount = rowCount + 1
Wend

ReDim rawData(rowCount, 2)

RowIndex = 2
nCount = 1

While sh.Range("BU" & RowIndex) <> ""
    
    If Right(sh.Range("BV" & RowIndex), 1) = "R" Then
        rawData(nCount, 1) = sh.Range("BU" & RowIndex)
        rawData(nCount, 2) = sh.Range("BV" & RowIndex).Formula
        nCount = nCount + 1
    End If
    RowIndex = RowIndex + 1
Wend

sh.Range("BU2:BV" & rowCount).ClearContents

For n = 1 To rowCount
    sh.Range("BU" & 1 + n) = rawData(n, 1)
    sh.Range("BV" & 1 + n) = rawData(n, 2)
Next n

End Sub

Let me know if this is of any use.

Andy