0

I have macro.In this it only remove data not rows. For example, if I have 600,000 records and after the macro runs I am left with 15000 records.but In my excel sheet is still showing 600,000 records: 15000 records with data and rest empty. I want it show only 15000 records.All empty row should be deleted. Here is my code: 

Sub DeleteRecord()
    Dim MySheet As String
    Dim cnt As Long
    MySheet = ActiveSheet.Name
    Cells(1, 1).CurrentRegion.AutoFilter Field:=33, Criteria1:= _
        ">=-.09", Operator:=xlAnd, Criteria2:="<=.01"


    cnt = Worksheets(MySheet).Cells.SpecialCells(xlCellTypeLastCell).Row
    If cnt > 3 Then Range("A2", ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.EntireRow.Delete
    End If
    Cells(1, 1).CurrentRegion.AutoFilter Field:=33
End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
Aditya Kaistha
  • 11
  • 4
  • Why did you just delete your code? Also, many of those reading the question will not know that 6 lakhs means 600,000. – John Coleman May 04 '20 at 11:03
  • Sorry I did not delete code.I recently join.Sub DeleteRecord() Dim MySheet As String Dim cnt As Long MySheet = ActiveSheet.Name Cells(1, 1).CurrentRegion.AutoFilter Field:=33, Criteria1:= _ ">=-.09", Operator:=xlAnd, Criteria2:="<=.01" cnt = Worksheets(MySheet).Cells.SpecialCells(xlCellTypeLastCell).Row If cnt > 3 Then Range("A2", ActiveCell.SpecialCells(xlLastCell)).Select Selection.EntireRow.Delete End If Cells(1, 1).CurrentRegion.AutoFilter Field:=33 End Sub – Aditya Kaistha May 04 '20 at 11:04
  • You accidentally deleted the code when you edited. I can roll it back if you can't figure out how to reverse it. – John Coleman May 04 '20 at 11:05
  • 1
    If you really have 600,000 records, you might want to use a database rather than Excel. – John Coleman May 04 '20 at 11:09
  • no After delete 15000 record left.I have 15000 records – Aditya Kaistha May 04 '20 at 13:14
  • I have 600000 records in excel.After run macro 15000 records left rest deleted.But in my excel sheet it show 15000 records with data rest are empty.Now I want to delete all empty rows.I only want 15000 records. – Aditya Kaistha May 04 '20 at 14:02
  • Maybe [this question](https://stackoverflow.com/q/9379673/4996248) will help. Note that some of those answers will run slowly in your use-case (the accepted answer is probably the worst of the lot) so look at them all before you decide which one is best. – John Coleman May 04 '20 at 14:18

0 Answers0