Unable to delete a blank row/s in a range due to text format from the dump file.

[Jonnathanjons]

Hello Team,
I am facing a challenge in running a script where I need to delete blank rows from a db2 dump file. but it looks like the my vba script below is not deleting blank rows due to the range in a text format . Some ranges are in text format . Hence I cannot change the text format to numbers before running this script as it will change all other records as you see in the ssheet.
Kindly suggest me a 1 line vba code which can delete the rows in blank even if they are in text format or general format or number format.
attaching the file

I prefer a 1 liner code like below which can work on such situations.

 

[Marc L]

Hello ! When what you call 'blank' is very not blank ‼​

my vba script below is not deleting blank rows due to the range in a text format

You are wrong as the cell A4 is just not empty like the error message explains : no matching cell !​

Like any Excel beginner can check for example with this formula in cell C4 : =LEN(A4) …​

Before to use the tricky SpecialCells method just check if the range contains at least a blank cell​

with an Excel worksheet function counting empty cells like in cell C1 : =COUNTBLANK(A1:A8) …​

So select A4, hit Del key to delete A4 content - now C1 is equal to one and C4 is equal to zero - then retry.​

​

Just correct how the data is imported with an appropriate better way !​

Or you can use the worksheet function TRIM to 'clean' at once the column …​

​

 

[Jonnathanjons]

Marc I am aware to check these whether a beginner or not.. my ask is to see if a vba script is available to delete these empty rows. possible

 

[Marc L]

If you really know to check so you can easily remove the unwanted ! What did you try at least ?​

​

So, if only you have well read my previous post explaining why your attachment does very not contain any empty row,​

what is your strategy in order to delete empty rows which do not exist ?‼ ​

​

As I gave you a way to clean such badly imported data, as a VBA procedure can use Excel basics like a formula …​

Or obviously the better in order to not have to clean anything is to just correctly import the data !​

​

 

[rlv01]

@Marc L is correct. The error message tells you what the problem is. .SpecialCells(xlCellTypeBlanks) only returns blank cells, and Cell A3 in your sample workbook is neither blank nor empty, which is why you got the error message . You have to clean the imported data first. Besides the several methods that Marc L listed, another way (and there are many) might be:

With ActiveSheet.Columns(1)
   .Replace What:=" ", Replacement:="", LookAt:=xlPart
   .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With

Obviously if the data in col A contains space characters that you want to keep, then the above method won't work and you will have try another way (of which there are many).

 

[Jonnathanjons]

@Marc L is correct. The error message tells you what the problem is. .SpecialCells(xlCellTypeBlanks) only returns blank cells, and Cell A3 in your sample workbook is neither blank nor empty, which is why you got the error message . You have to clean the imported data first. Besides the several methods that Marc L listed, another way (and there are many) might be:

With ActiveSheet.Columns(1)
   .Replace What:=" ", Replacement:="", LookAt:=xlPart
   .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With

Obviously if the data in col A contains space characters that you want to keep, then the above method won't work and you will have try another way (of which there are many).

Hello , Thanks for the response. I mentioned in my first note that its a db2 dump. it has 165-200 K rows. so you can imagine how long will it take for you to clean such records. Hence I ask for a script if available.. I wouldn't come here otherwise
So
I cannot manually do a delete as these are random cells over 200 K rows
I tried to trim these but these are loosing the format so the values are missing.example 00000248 become 248 which is not acceptable.

 

[Jonnathanjons]

If you really know to check so you can easily remove the unwanted ! What did you try at least ?​

​

So, if only you have well read my previous post explaining why your attachment does very not contain any empty row,​

what is your strategy in order to delete empty rows which do not exist ?‼ ​

​

As I gave you a way to clean such badly imported data, as a VBA procedure can use Excel basics like a formula …​

Or obviously the better in order to not have to clean anything is to just correctly import the data !​

​

it could be that you are underestimating the volume involved or did not follow when I said its a db2 dump which the format of the columns can be integer or Char or both and would run upto 160-200k rows..so a manual work is out of scope also I do not raise a request in this forum for something which I can do manually.

 

[rlv01]

Hence I ask for a script if available.

I provided one. Did you try it?

 

[Jonnathanjons]

I provided one. Did you try it?

Thanks .Yes that line did the magic. and the second line removed the empty cell.
Best part is its not changing the format of other rows. Thanks again.

.Replace What:=" ", Replacement:="", LookAt:=xlPart

.Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete