copy pste values aftr filtered data

[deletedalien]

Hey guys hi to all,

so here is a quick one that i can honestly not figure out,

so i have data in columns a - h

i filter data using column d

i need to copy paste values in column g to convert formulas to data

the macro runs fine

but after i check the cells the formulas are still there

i tried this using a named range from the top of the data (G2:G80)

but same result)

Range("GP").Select
 
Range("GP").Copy
 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("F2").Select
    ActiveSheet.ShowAllData
End Sub

also tried with unnamed ranges

Range("G2").Select
ActiveCell.Offset(1, 0).Select
 
        Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("F2").Select
    ActiveSheet.ShowAllData

 

[Rick Rothstein]

I am not sure how the filtering you are doing factors into your question, but if you have formulas in a column and want to convert them to the values they are displaying, you can use this line of code...

Columns("G").Value = Columns("G").Value

 

[deletedalien]

well...

if i step trough the code what it does is select the title of that column and when i tell the code to offset 1 row down it selects the filtered row (you can only see a thin line ) instead of selecting whatever cell is now directly below the title row,

in otherwords if title row ia G1 and G2 G3 G5 and G6 G7 are filtered out instead of selecting the first unfiltered cell G4 it selects the filtered cell G2 and tries to copy paste values and obviously SHOULD get an error saying that, copy and paste areas cannot overlap unless they are the same size and shape, blah blah blah.


on a side note i will always be filtering by the last date, so all the data that is filtered will be contiguous data and at the bottom of the data.

P.S. this code removed the formulas but left my cells blank

 

[Rick Rothstein]

Okay, what about this code then...

Sub ChangeFilteredFormulasToValues()
  Dim A As Range
  If ActiveSheet.FilterMode Then
    With Intersect(ActiveSheet.AutoFilter.Range.EntireRow, Columns("G")).SpecialCells(xlCellTypeVisible)
      For Each A In .Areas
        A.Value = A.Value
      Next
    End With
  End If
End Sub

 

[deletedalien]

Found it!!!


here is the solution

    Range("G1").Select
ActiveCell.Offset(1, 0).Select
Do While ActiveCell.EntireRow.Hidden = True
ActiveCell.Offset(1, 0).Select
Loop
        Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

Enjoy

 

[Rick Rothstein]

Found it!!!

here is the solution

That code works only because your data is contiguous... it would fail totally otherwise. The last code I posted works for your setup and will work for any other setup that can result from the filter.