none
How to delete formula from cells and keep the values in Excel VBA RRS feed

  • Question

  • Hi,

    In my Excel I have 15 columns. In column F which has a formula (INDEX MATCH), it has contains "RECEIVED" and "INTRANSIT". I need to filter the column F for all "RECEIVED" and then remove the formula from cells and retain or keep the values that are already in the cells. something tricky and i'm not sure on how to work on this in Excel VBA.

    Below is my initla VBA code:

    I already have the codes on how to filter. kindly please help me on how to do this. thank you in advance.

    Sub test_Click()
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim rng As Range
        Dim lrow As Long
    
        Set wb = ThisWorkbook
        Set ws = wb.Sheets("Intransit_")
        
        Application.ScreenUpdating = False
        
        ws.AutoFilterMode = False
        With ws
            lrow = .Range("F" & Rows.Count).End(xlUp).Row
            Set rng = .Range("A1:R" & lrow)
            Debug.Print rng.Address
            rng.AutoFilter Field:=6, Criteria1:="RECEIVED"
            
        End With
        Application.creenUpdting = True
    End Sub
    

    Tuesday, January 13, 2015 6:12 AM

Answers

  • 

    Solved.

    Sub test_Click()
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim rng As Range
        Dim lrow As Long
        Dim rRec As Range
        Dim btField As Byte
    
        Set wb = ThisWorkbook
        Set ws = wb.Sheets("Intransit_")
        btField = 6
        
        Application.ScreenUpdating = False
        
        ws.AutoFilterMode = False
        With ws
            lrow = .Range("F" & Rows.Count).End(xlUp).Row
            Set rng = .Range("A1:R" & lrow)
            With rng
                .AutoFilter Field:=btField, Criteria1:="RECEIVED"
                On Error Resume Next
                    Set rRec = .SpecialCells(xlCellTypeVisible)
                On Error GoTo 0
            End With
            .AutoFilterMode = False
            If Not rRec Is Nothing Then
                With rRec
                    .Columns(btField).Value = .Columns(6).Value
                End With
            End If
        End With
        Application.ScreenUpdating = True
    End Sub

    • Marked as answer by Lenoj Wednesday, January 14, 2015 12:17 AM
    Tuesday, January 13, 2015 8:22 AM

All replies

  • 

    Solved.

    Sub test_Click()
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim rng As Range
        Dim lrow As Long
        Dim rRec As Range
        Dim btField As Byte
    
        Set wb = ThisWorkbook
        Set ws = wb.Sheets("Intransit_")
        btField = 6
        
        Application.ScreenUpdating = False
        
        ws.AutoFilterMode = False
        With ws
            lrow = .Range("F" & Rows.Count).End(xlUp).Row
            Set rng = .Range("A1:R" & lrow)
            With rng
                .AutoFilter Field:=btField, Criteria1:="RECEIVED"
                On Error Resume Next
                    Set rRec = .SpecialCells(xlCellTypeVisible)
                On Error GoTo 0
            End With
            .AutoFilterMode = False
            If Not rRec Is Nothing Then
                With rRec
                    .Columns(btField).Value = .Columns(6).Value
                End With
            End If
        End With
        Application.ScreenUpdating = True
    End Sub

    • Marked as answer by Lenoj Wednesday, January 14, 2015 12:17 AM
    Tuesday, January 13, 2015 8:22 AM
  • Hi Lenoj,

    Thank you for sharing your solution here!


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, January 14, 2015 6:20 AM
    Moderator
  • Turn on the Macro Recorder. 

    Right-click > Copy > Right-click > Paste Special > Values.

    That will give you the code you need.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Saturday, January 17, 2015 11:00 PM