Add Semicolon to Last Cell in Row

Keddems · Aug 28, 2012

I would like to write a code that loops through my spreadsheet and adds a semicolon (“;”) to the last used cell in each row. I’m having trouble because the rows also contain fake blank cells (“ “). Here’s an example of what my table looks like:

[TABLE="class: MsoTableGrid"]
<tbody>[TR]
[TD="width: 83"]
[/TD]
[TD="width: 83"] Column1
[/TD]
[TD="width: 83"] Column2
[/TD]
[TD="width: 83"] Column3
[/TD]
[TD="width: 83"] Column4
[/TD]
[TD="width: 83"] Column5
[/TD]
[/TR]
[TR]
[TD="width: 83"] Row1
[/TD]
[TD="width: 83"] Apples
[/TD]
[TD="width: 83"] “ “
[/TD]
[TD="width: 83"] “ “
[/TD]
[TD="width: 83"] “ “
[/TD]
[TD="width: 83"] “ “
[/TD]
[/TR]
[TR]
[TD="width: 83"] Row2
[/TD]
[TD="width: 83"] Bananas
[/TD]
[TD="width: 83"] Pears
[/TD]
[TD="width: 83"] Apricots
[/TD]
[TD="width: 83"] “ “
[/TD]
[TD="width: 83"] “ “
[/TD]
[/TR]
[TR]
[TD="width: 83"] Row3
[/TD]
[TD="width: 83"] Grapes
[/TD]
[TD="width: 83"] Peaches
[/TD]
[TD="width: 83"] “ “
[/TD]
[TD="width: 83"] “ “
[/TD]
[TD="width: 83"] “ “
[/TD]
[/TR]
</tbody>[/TABLE]

So for example, in Row2/Column3, I want the contents of the cell to be “Apricots;”. Any suggestions about how to do this would be greatly appreciated!
Thanks!

iliace · Aug 28, 2012

Two assumptions: (1) the "fake blank" cells are actually blank, and there are no non-blank cells after them; and (2) this is the only thing that's on the spreadsheet, meaning there isn't some other tables or calculations on it.

Code:

Public Sub AddSemicolon()  Dim nRows As Long, nCols As Long
  Dim i As Long, j As Long
  Dim rng As Excel.Range
  
  Set rng = ActiveSheet.UsedRange
  nRows = rng.Rows.Count
  nCols = rng.Columns.Count
  
  For i = 1 To nRows
    For j = 1 To nCols
      If Len(rng.Cells(i, j + 1).Value) = 0 Then
        With rng.Cells(i, j)
          .Value = .Value & ";"
        End With
        j = nCols + 5
      End If
    Next j
  Next i
End Sub

Keddems · Aug 29, 2012

Hello and thanks so much for the reply. The code didn’t work for my data and I think it may be due to the assumptions. The file comes from MS Access and therefore has blank cells that still contain a space or other invisible content—hence, the “ “. Your code above, inserts the semicolon in the last row as follows:

[TABLE="class: MsoTableGrid"]
<tbody>[TR]
[TD="width: 73"]
[/TD]
[TD="width: 73"] Column1
[/TD]
[TD="width: 73"] Column2
[/TD]
[TD="width: 73"] Column3
[/TD]
[TD="width: 73"] Column4
[/TD]
[TD="width: 73"] Column5;
[/TD]
[/TR]
[TR]
[TD="width: 73"] Row1
[/TD]
[TD="width: 73"] Apples
[/TD]
[TD="width: 73"] “ “
[/TD]
[TD="width: 73"] “ “
[/TD]
[TD="width: 73"] “ “
[/TD]
[TD="width: 73"] “ “;
[/TD]
[/TR]
[TR]
[TD="width: 73"] Row2
[/TD]
[TD="width: 73"] Bananas
[/TD]
[TD="width: 73"] Pears
[/TD]
[TD="width: 73"] Apricots
[/TD]
[TD="width: 73"] “ “
[/TD]
[TD="width: 73"] “ “;
[/TD]
[/TR]
[TR]
[TD="width: 73"] Row3
[/TD]
[TD="width: 73"] Grapes
[/TD]
[TD="width: 73"] Peaches
[/TD]
[TD="width: 73"] “ “
[/TD]
[TD="width: 73"] “ “
[/TD]
[TD="width: 73"] “ “;
[/TD]
[/TR]
</tbody>[/TABLE]

Maybe what I need to do is delete the “ “ cells first and then run this code? I’m not sure how to do that. Also, in terms of your assumptions, there is no other data after the “fake” blank spaces.
Thanks for any advice!!!

iliace · Aug 29, 2012

If it's just one space, you could change this line:

If Len(rng.Cells(i, j + 1).Value) = 0

to

If Len(rng.Cells(i, j + 1).Value) <= 1

Or, you can have it trim as it goes, so above that If statement:

Rich (BB code):

Public Sub AddSemicolon()  Dim nRows As Long, nCols As Long
  Dim i As Long, j As Long
  Dim rng As Excel.Range
  Dim s As String
  
  Set rng = ActiveSheet.UsedRange
  nRows = rng.Rows.Count
  nCols = rng.Columns.Count
  
  For i = 1 To nRows
    For j = 1 To nCols
      With Application.WorksheetFunction
        s = .Clean(.Trim(rng.Cells(i, j + 1).Value))
      End With
      If Len(s) = 0 Then
        With rng.Cells(i, j)
          .Value = .Value & ";"
        End With
        j = nCols + 5
      End If
    Next j
  Next i
End Sub

Keddems · Aug 30, 2012

Fabulous! That worked beautifully. Thank you!

Add Semicolon to Last Cell in Row

Keddems

New Member

Excel Facts

iliace

Well-known Member

Keddems

New Member

iliace

Well-known Member

Keddems

New Member

Similar threads

Forum statistics

Share this page

Add Semicolon to Last Cell in Row

Keddems

New Member

Excel Facts

iliace

Well-known Member

Keddems

New Member

iliace

Well-known Member

Keddems

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock