0

I have a small script that sort of does what I need it to do, but I'm afraid at some point there will be more than 4 characters in a cell and I don't want to delete it. The logic that I want to employ is as follows:

If any cell in BB1:BB10 contains ONLY Chr(10) then move the contents of the cells below up one cell. Something like this

Public Sub CheckHisMethod()
    Dim i As Integer
    i = 1
    For i = 10 To 1 Step -1
        If Excel.ActiveSheet.Range("BB" & i).Value = Chr(10) Then        ' or =vblf or =chr$(10)
            Excel.ActiveSheet.Range("BB" & i).Delete Shift:=xlUp
        End If
    Next i
    MsgBox "Done"
End Sub

But...I don't want to delete the Chr(10) from each cell, I only want to delete the cell, and move the cell below up one cell, if the cell contains ONLY Chr(10). How can I do that?

Community
  • 1
  • 1
ASH
  • 20,759
  • 19
  • 87
  • 200
  • 4
    What's the problem with your solution? – Wagner DosAnjos Jan 12 '17 at 14:51
  • Yeah, it's a little hard to explain. I have cells in an array with 1, 2, 3, 4, or more Chr(10) characters. I have cells with text and Chr(10) characters. I want to delete cells with ONLY 1, 2, 3, 4, or more Chr(10) characters. Does that make sense? – ASH Jan 12 '17 at 15:08
  • 1
    Probably a pair of screenshots would help. E.g. Input and expected output. – Vityata Jan 12 '17 at 15:11

2 Answers2

3

Please try the following. It removes all CHR(10) and then it checks if the length of the resulting string is 0, meaning all characters in the cell are CHR(10).

Public Sub CheckHisMethod()
    Dim i As Integer
    i = 1
    For i = 10 To 1 Step -1
        If Len(Replace(Excel.ActiveSheet.Range("BB" & i).Value,Chr(10),"")) = 0 Then        ' or =vblf or =chr$(10)
            Excel.ActiveSheet.Range("BB" & i).Delete Shift:=xlUp
        End If
    Next i
    MsgBox "Done"
End Sub
Wagner DosAnjos
  • 6,304
  • 1
  • 15
  • 29
1

I'd personally use a regular expression for this - it will likely be much faster than other string manipulations:

'Add a reference to Microsoft VBScript Regular Expressions 5.5
Public Sub CheckHisMethod()
    Dim i As Integer
    With New RegExp
        .Pattern = "^[\n]+$"
        .MultiLine = True
        For i = 10 To 1 Step -1
            If .Test(Excel.ActiveSheet.Range("BB" & i).Value) Then
                Excel.ActiveSheet.Range("BB" & i).Delete Shift:=xlUp
            End If
        Next i
    End With
    MsgBox "Done"
End Sub
Comintern
  • 21,855
  • 5
  • 33
  • 80
  • Note that `RegExp` is not available in Excel VBA by default. http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops – Wagner DosAnjos Jan 12 '17 at 19:35
  • 1
    @wdosanjos - Note that the comment mentions that it requires a reference to Microsoft VBScript Regular Expressions 5.5. If for some reason you wanted to late bind it, you can replace the `New RegExp` call with a call to `CreateObject("VBScript.RegExp")`. – Comintern Jan 12 '17 at 19:38