Delete filter data except header



'Delete filter data except header
Sub redfdf()

Sheets(1).Range("a1").Select
Selection.AutoFilter
Sheets(1).UsedRange.AutoFilter Field:=2, Criteria1:="Income"
Sheets(1).UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy Sheets(2).Range("A2")
Sheets(1).UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).Select
Selection.EntireRow.Delete

End Sub

Comments

Post a Comment

Popular posts from this blog

Check folder exist or not in excel vba

Check if Folder Exists using Excel VBA 'In this Example I am checking if "C:\Temp" exits Sub sbCheckingIfAFolderExists() Dim FSO Dim sFolder As String sFolder = "C:\Temp" ' You can Specify Any Folder To Check It Set FSO = CreateObject("Scripting.FileSystemObject") If FSO.FolderExists(sFolder) Then MsgBox "Specified Folder Is Available", vbInformation, "Exists!" Else MsgBox folder &"Specified Folder Not Found", vbInformation, "Not Found!" End If End Sub ------------------------ Opening Folders using VBA Excel 'In this Example I am Opening a Folder ("C:\Temp") Sub sbOpeningAFolder() Dim FSO Dim sFolder As String sFolder = "C:\Temp" 'You can specify your Folder which you wants to Open Set FSO = CreateObject("Scripting.FileSystemObject") If Not FSO.FolderExists(sFolder) Then MsgBox "Specified Folder Not Found", vbInformation, ...
Read more

number format

  Selection.NumberFormat = "0.00" --------------- =OFFSET(G1094,-1,0)/OFFSET(G1094,-12,0)-1 ---------------- Sub test() ' to select all sheets in the summary workbook Dim i As Long Sheet1.Select For i = 2 To ThisWorkbook.Sheets.Count     If Sheets(i).Name <> "Summary" Then Sheets(i).Select Replace:=False Next i End Sub  --------------- Sub LastRowInOneColumn()  'Find the last used row in a Column: column F in this     Dim LastRow As Long     With ActiveSheet         LastRow = .Cells(.Rows.Count, "F").End(xlUp).Row     End With     MsgBox LastRow End Sub --------------------------------- Sub tests() ' to place vlookup in each and every sheet other than summary Dim i As Long Sheet1.Select For i = 2 To ThisWorkbook.Sheets.Count     If Sheets(i).Name <> "Summary" Then Sheets(i).Select Replace:=False     a = Range("f7").End(xlDown).Row ...
Read more

Hide sheet, filter data, fill formula, select visible cells

To add sheets: Sheets.Add after:=Sheets(Sheets.count) ---------------------------------------- Sub togetsheenames() Dim i As Integer Dim sh As String For i = 1 To Sheets.Count sh = Sheets(i).Name Sheets("ToDelete").Range("A" & i) = sh Next End Sub -------------------------- fill lookup formula  Range("D3").Select ActiveCell.Formula = "=VLOOKUP(A3,Exp!$B:$E,4,0)"     Range("d3").AutoFill Range("d3:d" & Range("a1").End(xlDown).Row) ------------------ Hide sheet Sheets("Sheet2").Visible = True unhide sheet  Sheets("Sheet2").Visible = Flase --------------------------------------------- For i = 2 To drcount mystng = Sheets("Counter").Range("A" & i).Value LR = Sheets("Rough").Range("A1").CurrentRegion.Rows.Count + 1 Sheets(2).Select Sheets(2).Rows("3:3").AutoFilter Field:=26, Criteria1:=m...
Read more