Deleting Manual Numbers

[Ramneek]

Hi Experts,

I prepare monthly MIS reports wherein every month in several tabs of excel sheet, i need to delete manual numbers. I need a separate VBA code for each sheet wherein code will loop through each and every cell let's say, in tab "A" of column B to Z and row 1 to 1000 and if its find the manual numbers (not containing any formula or text) , it will delete the numbers.

I need a dummy code and then i will customize it according to the requirement of every sheet.

Thanks in advance.

 

[Peter_SSs]

Give this a try with a copy of your workbook as a starting point.

Sub Delete_Nums()
  Dim ws As Worksheet
  
  On Error Resume Next
  For Each ws In Worksheets
    ws.Range("B1:Z1000").SpecialCells(xlConstants, xlNumbers).ClearContents
  Next ws
  On Error GoTo 0
End Sub

 

[Ramneek]

Hi Sir,
Thanks for your help. Actually, I am too naïve in VBA programming and can do only small things using it.

I understand from the code, that it will loop through every worksheet in the excel file. But, could you please provide the code assuming that I have only tab (worksheet) in the excel file and the code will loop through the cells of the single worksheet only. and then i will customize the code for every sheet.

Thanks

 

[Peter_SSs]

This would work on the first worksheet in the workbook only.
There should be no need for a loop which would only slow the code considerably.

Sub Delete_Nums_v2()
  On Error Resume Next
  Sheets(1).Range("B1:Z1000").SpecialCells(xlConstants, xlNumbers).ClearContents
  On Error GoTo 0
End Sub

 

[Ramneek]

Hi Sir,

Thanks a lot. It will save the time and energy massively in deleting the number. Thanks a ton for educating that there is no need of looping in this case.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

Post back here if you end up needing help to apply it to multiple sheets.