How to delete rows and tabs

[zombiemaster]

I used to know some of this stuff but have been out of the VBA game for a few years...sorry to have to ask such a simple question!

I have a workbook that gets created each day with varying numbers of rows each day. Column C will have a numeric value. I need to delete all the rows with a numeric value greater than zero.

I currently do this manually by sorting A>Z, find the first row in the list with a number greater than zero, then deleting all the rows below that, but want to include VBA to do this step for me.

ALSO, there are multiple tabs in the workbook, and I delete all of them except the first tab. Is there a way to delete all the tabs except the first one named "Original Data"?

Thanks for your help,
-=ZM=-

 

[Peter_SSs]

I have a workbook that gets created each day with varying numbers of rows each day.

Approximately how many rows would there be on a 'big' day?

 

[zombiemaster]

Approximately how many rows would there be on a 'big' day?

Usually less than 500 - but I would like to assume it could get up to 1500 just as a buffer.

 

[Peter_SSs]

Thanks. In the overall scale of Excel, that is a fairly small number, so give this a try with a copy of your data.
I have assumed a header row

Sub DelPositiveRows()
  Application.ScreenUpdating = False
  With Range("C2", Range("C" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("if(isnumber(#),if(#>0,True,#),if(#="""","""",#))", "#", .Address))
    On Error Resume Next
    .SpecialCells(xlConstants, xlLogical).EntireRow.Delete
    On Error GoTo 0
  End With
  Application.ScreenUpdating = True
End Sub

 

[zombiemaster]

Thanks. In the overall scale of Excel, that is a fairly small number, so give this a try with a copy of your data.
I have assumed a header row

Sub DelPositiveRows()
  Application.ScreenUpdating = False
  With Range("C2", Range("C" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("if(isnumber(#),if(#>0,True,#),if(#="""","""",#))", "#", .Address))
    On Error Resume Next
    .SpecialCells(xlConstants, xlLogical).EntireRow.Delete
    On Error GoTo 0
  End With
  Application.ScreenUpdating = True
End Sub

Unfortunately, nothing happened...tried it a couple of times, but it didn't do anything at all. Thoughts?

-=ZM=-

 

[Peter_SSs]

Unfortunately, nothing happened...tried it a couple of times, but it didn't do anything at all. Thoughts?

A likely possibility is that those numbers are not actual numbers but text. What happens if you put this formula in a vacant cell?
Where I have C5, make sure that it is point at one of the "numbers" greater than zero in column C
=ISNUMBER(C5)

 

[zombiemaster]

A likely possibility is that those numbers are not actual numbers but text. What happens if you put this formula in a vacant cell?
Where I have C5, make sure that it is point at one of the "numbers" greater than zero in column C
=ISNUMBER(C5)

Yes, you're right - it comes up as FALSE. I changed the format of the column to Number but it didn't help...until I double-clicked in C5 and pressed enter, then it updated to TRUE. I have code somewhere to do that automatically for a range, but that was years ago that I had it, and have changed jobs since then so probably don't have access to the code...

 

[Peter_SSs]

Leave them as text and try this instead.

Sub DelPositiveRows()
  Application.ScreenUpdating = False
  With Range("C2", Range("C" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("if(isnumber(#+0),if(#+0>0,True,#),if(#="""","""",#))", "#", .Address))
    On Error Resume Next
    .SpecialCells(xlConstants, xlLogical).EntireRow.Delete
    On Error GoTo 0
  End With
  Application.ScreenUpdating = True
End Sub

 

[zombiemaster]

Leave them as text and try this instead.

Sub DelPositiveRows()
  Application.ScreenUpdating = False
  With Range("C2", Range("C" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("if(isnumber(#+0),if(#+0>0,True,#),if(#="""","""",#))", "#", .Address))
    On Error Resume Next
    .SpecialCells(xlConstants, xlLogical).EntireRow.Delete
    On Error GoTo 0
  End With
  Application.ScreenUpdating = True
End Sub

That did the trick, thank you!!

Also, if you have time to look at the 2nd part of my issue, I have multiple tabs and only need to keep the one called "Original Data" and delete any others.
Thanks!
-=ZM=-

 

[Peter_SSs]

Two codes below. The first deletes all sheets except the first. The second code deletes all sheets except "Original Data". Second would be safest I would say in case the order of sheets happens to have been changed.

Sub DeleteSheets_1()
  Dim i As Long
  
  Application.DisplayAlerts = False
    For i = Sheets.Count To 2 Step -1
      Sheets(i).Delete
    Next i
  Application.DisplayAlerts = True
End Sub

Sub DeleteSheets_2()
  Dim ws As Worksheet
  
  Application.DisplayAlerts = False
    For Each ws In Worksheets
      If ws.Name <> "Original Data" Then ws.Delete
    Next ws
  Application.DisplayAlerts = True
End Sub