Not sure how the number 0 was added to a cell when using a macro to add a blank row to excel file VBA

[zack8576]

I have this macro that adds an empty row (only column A will contain a value same as the cell above it) between two rows that contain different values in column B
in the example file below, a new row is added after rows: 14, 20, 27. The code is doing everything that it was intended to do except it adds "0" to column K in the new row...
any idea what is causing this ? Thanks !

Sub BlankSpaceBetweenStructures()
    Dim lr As Long, r As Long
    Application.ScreenUpdating = False

    lr = Cells(Rows.Count, "B").End(xlUp).Row

    For r = lr To 3 Step -1

        If Cells(r, "B").Value <> Cells(r - 1, "B").Value Then

            Rows(r).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

            Cells(r, "A").Value = Cells(r - 1, "A").Value
        End If
    Next r
    Application.ScreenUpdating = True
End Sub

 

[Joe4]

Are the values in column K hard-coded, or are they formulas?

 

[zack8576]

Are the values in column K hard-coded, or are they formulas?

the value in K are exported from a software to a csv file, there is no formula in any cell in these files.

 

[Joe4]

Can you take a look at one of those cells with a 0 in column K, and see how the cell is formatted?
Is there some sort of Custom Formatting or Conditional Formatting on the cell?

Also, it looks like K14 is one of these cells with a zero in it.
If you enter the following formula in any unused cell on your sheet, please tell me what it returns?

=LEN(K14)

 

[zack8576]

Can you take a look at one of those cells with a 0 in column K, and see how the cell is formatted?
Is there some sort of Custom Formatting or Conditional Formatting on the cell?

Also, it looks like K14 is one of these cells with a zero in it.
If you enter the following formula in any unused cell on your sheet, please tell me what it returns?

=LEN(K14)

I did not see any special formatting, and there is 0 conditional formatting rules.
here is a file, see dropbox link below

XXXXXX.csv

Shared with Dropbox

www.dropbox.com

 

[Joe4]

I cannot download files from my current location. So I cannot view your file.

Can you try what I asked at the end of my previous post and tell me the answer?
Are you seeing these zeroes in the Excel file right after you run the code?
Or are you seeing them when you open the CSV file?

 

[zack8576]

Can you try what I asked at the end of my previous post and tell me the answer?
Are you seeing these zeroes in the Excel file right after you run the code?
Or are you seeing them when you open the CSV file?

the length returned as 1
these zeroes are seen right after I ran the code, as well as when I open the modified csv (after the code is ran)
these zeroes are showing up in all added rows, in every of the files I've tested.

 

[Joe4]

These are in data tables, are they?
Are you sure that there is no other code running (like a Worksheet_Change event?

I tried manually create a data file myself, and running your code against, and it worked as expected (no zeroes).
So I do not see anything in your code that should be doing that. I am thinking it has to be some other code interacting with it, or something else on the sheet.

If you can provide a link to an example file BEFORE you run the code, then a little while later when I am at my home computer, I can download the file and try running your code on it, and see what happens for myself, and see if I can tell what is going on.

 

[zack8576]

These are in data tables, are they?
Are you sure that there is no other code running (like a Worksheet_Change event?

I tried manually create a data file myself, and running your code against, and it worked as expected (no zeroes).
So I do not see anything in your code that should be doing that. I am thinking it has to be some other code interacting with it, or something else on the sheet.

If you can provide a link to an example file BEFORE you run the code, then a little while later when I am at my home computer, I can download the file and try running your code on it, and see what happens for myself, and see if I can tell what is going on.

Joe, I think you just found the issue
0 was added to the files by another subroutine (this subroutine is run after the one I posted earlier)
This raised another question, why would the following subroutine add a 0 to a cell that has a LEN of 0, when the purpose of the routine is shorten values longer than 40 in any cell in K ?

Sub CleanUpStringsLongerThanForty()
   With Range("K2", Range("K" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("if(len(#)>40,substitute(substitute(substitute(#,""(San)"",""""),""(WET)"",""""),""COVER"",""""),#)", "#", .Address))
  End With
End Sub

 

[Joe4]

Joe, I think you just found the issue
0 was added to the files by another subroutine (this subroutine is run after the one I posted earlier)
This raised another question, why would the following subroutine add a 0 to a cell that has a LEN of 0, when the purpose of the routine is shorten values longer than 40 in any cell in K ?

Sub CleanUpStringsLongerThanForty()
   With Range("K2", Range("K" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("if(len(#)>40,substitute(substitute(substitute(#,""(San)"",""""),""(WET)"",""""),""COVER"",""""),#)", "#", .Address))
  End With
End Sub

I am glad you found it.

Your latest query is a different question altogether (evaluating a whole different bit of code), and it would be best to post that in a new thread as its own question.
(That is a not a method I use at all, so I probably would not be the best person to answer this question anyhow).