Bold & add line after subtotal row

[Guest]

I searched messages and found the following code by Chuck from 10/6/06 and
would like to adapt it to my spreadsheet, but can't get it to work. I
changed lastrow = Range ("L" & Rows.Count).End(xlUP).Row from "L" to "G" as
this is the column where my totals are. Will someone please explain this
code, especially the InStr lines? Hopefully, if I understand it better maybe
I can figure out what I'm doing wrong the reason it won't work.

Sub AddRowSubTotalsAssignedTo()
Dim lastrow As Long
Dim r As Long
lastrow = Range("L" & Rows.Count).End(xlUp).Row '"L" is the column that
'contains the GrandTotal
For r = lastrow To 2 Step -1
If InStr(1, Cells(r, 3).Value, "Total") > 0 Or _
InStr(1, Cells(r, 8).Value, "Total") > 0 Or _
InStr(1, Cells(r, 12).Value, "Total") > 0 Then
Range(Cells(r, 1), Cells(r, 30)).Font.Bold = True '30 is number
'of columns from "A" that the macro will BOLD
ActiveSheet.Rows(r + 1).EntireRow.Insert
End If
Next
End Sub

Any help is greatly appreicated!
Thanks,
Pam

 

[Bernie Deitrick]

Pam,

If InStr(1, Cells(r, 3).Value, "Total") > 0 Or _

InStr(1, Cells(r, 8).Value, "Total") > 0 Or _
InStr(1, Cells(r, 12).Value, "Total") > 0 Then
Range(Cells(r, 1), Cells(r, 30)).Font.Bold = True


This code looks for the word Total in column C (3rd column), column H (8th column), or column L
(12th column), and then bolds any row with Total any of those columns from column A to column AD.

You would probably want

If InStr(1, Cells(r, 7).Value, "Total") > 0 Then
Cells(r, 1).EntireRow.Font.Bold = True
End If

which will look for the word Total in column G, and bold the entire row when that is true.

HTH,
Bernie
MS Excel MVP

 

[Guest]

Thanks, Bernie, for the explanation. This will help me to apply this code to
my projects.

 

[Guest]

Bernie,

I may have spoke too soon. I did get it to work for the first part of my
spreadsheet, but can you tell me how to get it to work for a named range
(just one section at a time)?

Thanks again for your help.
Pam

 

[Bernie Deitrick]

Pam,

You need to post your code, explain what your named range is (what range it refers to), and what you
want to do - a bit more explanation, in short...

HTH,
Bernie
MS Excel MVP

 

[Guest]

Bernie,

Sorry about that. I have a spreadsheet starting with column A contains
Tech, JobNumber, Type, Code, StartTime, StopTime, and CalculatedTime. I have
it sorted by Tech. I ran subtotal for each change in JobNumber (for each
Tech - I'm working each section separately because there are other
calculations needed in each Tech grouping) to sum CalculatedTime. I wanted to
bold subtotal row and add a row after. The explanation and tweaking you
provided earlier worked great for the first Tech when I only had one set of
subtotals. I moved to the second Tech section and ran code and it inserted
another row under each subtotal for the first Tech. So I thought I would
name each section. Currently working with "DarrylJobs" (A218:G491). Column
C has the word total in it for each subtotal.
Here is the code I'm using:

Sub AddRowSubTotalsAssignedTo()
Dim lastrow As Long
Dim r As Long
lastrow = Range("D" & Rows.Count).End(xlUp).Row
For r = lastrow To 2 Step -1
If InStr(1, Cells(r, 7).Value, "Total") > 0 Then
Range(Cells(r, 1), Cells(r, 7)).Font.Bold = True
Range ActiveSheet.Rows(r + 1).EntireRow.Insert
End If
Next
End Sub

Thanks,
Pam

 

[Bernie Deitrick]

Pam,

Change this

If InStr(1, Cells(r, 7).Value, "Total") > 0 Then

to

If InStr(1, Cells(r, 3).Value, "Total") > 0 Then

3 is column C - the syntax of the Cells() range object is Cells(Row#, Colum#), and it indicates a
single cell.

If you want to have code that finds Total in any column, then use something like

Sub AddRowSubTotalsAssignedTo2()
Dim lastrow As Long
Dim r As Long
lastrow = Range("D" & Rows.Count).End(xlUp).Row
For r = lastrow To 2 Step -1
If Application.WorksheetFunction.CountIf(Rows(r), "*Total*") > 0 Then
Range(Cells(r, 1), Cells(r, 7)).Font.Bold = True
ActiveSheet.Rows(r + 1).EntireRow.Insert
End If
Next r
End Sub

HTH,
Bernie
MS Excel MVP

 

[Guest]

Bernie,

I really appreciate you helping me with this. I don't think I explained
myself well in the last post. I was able to figure out with your previous
explanation how to change the code to the column with "Total" in it. What I
need now is to know how to limit the code to a portion of the spreadsheet to
add rows and bold. Like section a150:g400. Does this make sense? I hope
I'm explaining it right. I've included new code I've tried, but get error
"Type Mismatch". I hope you won't mind taking a look to see what is wrong?

Sub AddRowSubTotalsAssignedTo()
Dim lastrow As Long
Dim r As Long
lastrow = Range("D" & Rows.Count).End(xlUp).Row
For r = lastrow To 2 Step -1
If InStr(1, Cells("d197:d209").Value, "Total") > 0 Then
Range(Cells("a197:a209"), Cells("g197:g209")).Font.Bold = True
Range(Cells("a197:g209")).Rows(r + 1).EntireRow.Insert
End If
Next
End Sub

Thanks again,
Pam

 

[Bernie Deitrick]

Sub AddRowSubTotalsAssignedTo()
Dim r As Long
Dim LastRow As Long
Dim FirstRow As Long
Dim myR As Range

Set myR = Range("A150:G400")
LastRow = myR.Cells(myR.Cells.Count).Row
FirstRow = myR.Cells(1).Row

For r = LastRow To FirstRow Step -1
If InStr(1, Cells(r, 4).Value, "Total") > 0 Then
Range(Cells(r,1), Cells(r,7)).Font.Bold = True
Cells(r+1,1).EntireRow.Insert
End If
Next
End Sub

 

[Guest]

Bernie,

It worked perfect!! Thank you so much for your time and help.
Greatly appreciated!!
Pam

P.S. Are there any books or sites you can recommend to better understand
aspects of coding? Seems there is a lot more you can do if using code.