Find Text and Resize Row Height - VBA

[jwb1012]

Hello, I am attempting to find a code that goes through all worksheets in activeworkbook and searches column C for the term "Method of Quoting:", when it is found, I would like to resize that row height to 100.

Is this possible?

 

[My Aswer Is This]

Try this:

Sub Row_Height()
Application.ScreenUpdating = False
Dim i As Long
Dim b As Long
Dim Lastrow As Long
    
    For b = 1 To Sheets.Count
        Lastrow = Sheets(b).Cells(Rows.Count, "C").End(xlUp).Row
        For i = 1 To Lastrow
            If Sheets(b).Cells(i, "C").Value = "Method of Quoting" Then Sheets(b).Cells(i, 3).RowHeight = 100
        Next
    Next
Application.ScreenUpdating = True
End Sub

 

[jwb1012]

Thanks Rick. This worked for for me. I also got it to loop through all worksheets.

Any other thoughts on the scenario with multiple occurrences? I may have spoken too soon on that - looks like there are situations where it could appear twice.

Sub EnlargeMethodOfQuoting_2()
    For Each Sh In ActiveWorkbook.Sheets
        If Left(Sh.Name, 9) = "Labor BOE" Then
        On Error Resume Next
            Sh.Columns("C").Find("Method of Quoting:", , xlValues, xlWhole).RowHeight = 100
        On Error GoTo 0
        End If
     Next Sh
End Sub

 

[jwb1012]

I tried this modified version of your code. I saw it loop through all of the worksheets, replace "Method of Quoting:" with #N/A and then back to "Method of Quoting" - but it didn't resize. Didn't result in error likely due to my on error lines, but no resize.

Sub EnlargeMethodOfQuoting_3()
    For Each Sh In ActiveWorkbook.Sheets
        If Left(Sh.Name, 9) = "Labor BOE" Then
        On Error Resume Next
            Columns("C").Replace "Method of Quoting:", "#N/A", xlWhole
            [U]Columns("C").SpecialCells(xlConstants, xlErrors).RowHeight = 100[/U]
            Columns("C").Replace "#N/A", "Method of Quoting:", xlWhole
        On Error GoTo 0
        End If
     Next Sh
End Sub

 

[My Aswer Is This]

Where in your original post did you want this?

If Left(Sh.Name, 9) = "Labor BOE" Then

Did you attempt to use my script?

 

[jwb1012]

Sorry, this was an addition Rick helped me with. Yes for some reason it is not working. I tried two versions of your code - neither seem to work.. I don't understand...

For some reason the formatting of the cell keeps putting a " ' " before the text. Is this possibly the cause? I tried deleting it. It came right back..

Sub Row_Height()
Application.ScreenUpdating = False
Dim i As Long
Dim b As Long
Dim Lastrow As Long
    
    For b = 1 To Sheets.Count
        Lastrow = Sheets(b).Cells(Rows.Count, "T").End(xlUp).Row
        For i = 1 To Lastrow
            If Sheets(b).Cells(i, "C").Value = "Method of Quoting" Then Sheets(b).Cells(i, 3).RowHeight = 100
        Next
    Next
Application.ScreenUpdating = True
End Sub

THen,..version 2:

Sub Row_Height_2()
Application.ScreenUpdating = False
Dim i As Long
Dim b As Long
Dim Lastrow As Long
Dim Sh As Worksheet
     For Each Sh In ActiveWorkbook.Sheets
        If Left(Sh.Name, 9) = "Labor BOE" Then
                Lastrow = Sh.Cells(Rows.Count, "T").End(xlUp).Row
                For i = 1 To Lastrow
                    If Sh.Cells(i, "C").Value = "Method of Quoting" Then Sheets(b).Cells(i, 3).RowHeight = 100
                Next
        End If
     Next Sh
Application.ScreenUpdating = True
End Sub

 

[jwb1012]

Thanks for the help. Was able to get the code below to work as intended:

Sub EnlargeMethodOfQuoting()
Dim Sh As Worksheet
Dim X As Integer
Dim End_Row As Integer
    For Each Sh In ActiveWorkbook.Sheets
        If Left(Sh.Name, 9) = "Labor BOE" Then
                
        Dest_Lastrow = Sh.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
        Dest_LastColumn = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count
                
            For lRow = Dest_Lastrow To 3 Step -1
                With Sh.Cells(lRow, "C")
                    On Error Resume Next
                        .Find("Method of Quoting:", , xlValues, xlWhole).RowHeight = 100
                    On Error GoTo 0
                End With
                
            Next lRow
        End If
    Next Sh
End Sub

 

[My Aswer Is This]

Well I cannot speak for the second code. Most times I never understand "Ricks" code

If your saying there may be a " ' " before the text "Method of Quoting"

Then change my script to this:


If Sheets(b).Cells(i, "C").Value = "'Method of Quoting" Then Sheets(b).Cells(i, 3).RowHeight = 100

Just add that little " ' " before Method of Quoting"

See if you want to learn Vba you should read the code and make simple changes like.


My script worked for me but you said "'Method of Quoting" Not "''Method of Quoting"