Bold ends

[vegas808]

I'm wondering if this program could be optimized for speed if I have 1000+ rows. Here's my simple program. I have 3 columns, on a worksheet, I really want to have this formatting. See my attached samples

Sub BoldEnds()

Dim rw As Long
Dim endrw As Long: endrw = Range("C" & Rows.count).End(xlUp).Row

Application.ScreenUpdating = False

For rw = 11 To endrw

With Range("C" & rw)
.Font.Bold = False
.Characters(start:=1, Length:=InStr(1, .Value, "-") - 1).Font.Bold = True
.Characters(Len(.Value) - 1, 2).Font.Bold = True
End With

Next rw

Application.ScreenUpdating = True

End Sub

 

[Micron]

The only thing I can think of is to bold it all outside of the loop, then un-bold from the first dash to the last one inside the loop. That would eliminate one formatting step and a loop to un-bold. I suspect you won't notice any difference until you start getting up to something like 5 to 10 thousand rows though.
EDIT - If the numeric portion is always the same length, could use Mid function.

 

[Micron]

Actually, could use it regardless, but maybe not necessary or of much help. Working on something . . .

 

[Micron]

10,000 rows took about 6 seconds:

Sub BoldEnds()

Dim rw As Long, endrw As Long
Dim strStart As Integer, strEnd As Integer

endrw = Cells(Rows.count, "C").End(xlUp).Row
Application.ScreenUpdating = False
Range("C1:C" & endrw).Font.Bold = True

For rw = 11 To endrw
    With Range("C" & rw)
        strStart = InStr(.Value, "-") + 1
        strEnd = InStrRev(.Value, "-")
        .Characters(strStart, strEnd - strStart).Font.Bold = False
    End With
Next rw

Application.ScreenUpdating = True

End Sub

You could speed it up even more if you didn't start at C1. Instead, perhaps start with active cell or row, or use SelectionChange event to run from the selection. Anything that doesn't loop over rows that are already formatted this way.

 

[vegas808]

The only thing I can think of is to bold it all outside of the loop, then un-bold from the first dash to the last one inside the loop. That would eliminate one formatting step and a loop to un-bold. I suspect you won't notice any difference until you start getting up to something like 5 to 10 thousand rows though.
EDIT - If the numeric portion is always the same length, could use Mid function.

Thanks for your input.

"If the numeric portion is always the same length, could use Mid function."
The 2nd and 4th strings are at constant size.

 

[Micron]

Numeric portion, not the string. Doesn't matter since the code I posted finds the first and last "-" and calculates the length from that. I doubt you could use Mid with the Characters property anyway.

 

[Peter_SSs]

10,000 rows took about 6 seconds:

Wow. I have a pretty old machine so was expecting that time or longer but it did 10,000 rows for me in just under 1 second.
The code below (it accesses the worksheet less often) was about 10% faster when timed accurately, but at both under 1 second, on my machine the difference is not readily noticeable.

Sub Bold_Ends()
  Dim a As Variant
  Dim i As Long, st As Long
 
  Application.ScreenUpdating = False
  With Range("C11", Range("C" & Rows.Count).End(xlUp))
    .Font.Bold = True
    a = .Value
    For i = 1 To UBound(a)
      st = InStr(1, a(i, 1), "-")
      .Cells(i).Characters(st, InStrRev(a(i, 1), "-") - st + 1).Font.Bold = False
    Next i
  End With
  Application.ScreenUpdating = True
End Sub

 

[Micron]

That looks pretty slick. If you don't mind answering a few questions I'd appreciate it.
With this: a = .Value
you assign the entire range contents to an array in one go? If so, what if some cells are blank? Do those end up in an array of variants as Nulls or are they skipped?
The first element index in the array is one (not zero) because this is an array of ranges (cells)? I think I've got to read up on that array type.
TIA.

 

[Peter_SSs]

you assign the entire range contents to an array in one go?

Yes, it reduces the number of interactions between the code and the worksheet.

If so, what if some cells are blank? Do those end up in an array of variants as Nulls or are they skipped?

That element in the array will be empty.

The first element index in the array is one (not zero)

Yes.