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?

 

[Rick Rothstein]

...and searches column C for the term "Method of Quoting:"

Is that the only text in the cell or could there be other text in the cell as well?

 

[jwb1012]

That will always be the only text in the cell in column C.

 

[Rick Rothstein]

That will always be the only text in the cell in column C.

Give this macro a try...

Sub EnlargeMethodOfQuoting()
  Columns("C").Replace "Method of Quoting:", "#N/A", xlWhole
  Columns("C").SpecialCells(xlConstants, xlErrors).RowHeight = 100
  Columns("C").Replace "#N/A", "Method of Quoting:", xlWhole
End Sub

 

[jwb1012]

Hmm.. it replaces my "Method of Quoting:" with #N/A and then results in "Run-time Error 1004: No cells were found on the second line (adjusting row height)

 

[Rick Rothstein]

Hmm.. it replaces my "Method of Quoting:" with #N/A and then results in "Run-time Error 1004: No cells were found on the second line (adjusting row height)

What is in the cells of Column C, constants or formulas? If formulas, change that second line to this...

Columns("C").SpecialCells(xlFormulas, xlErrors).RowHeight = 100

 

[jwb1012]

All values are text. No formulas

 

[Rick Rothstein]

All values are text. No formulas

What version of Excel are you using?

How many rows of data are you processing?

 

[jwb1012]

Excel 2013. Right now there are 200 rows. Method of quoting only appears once though.

 

[Rick Rothstein]

Excel 2013. Right now there are 200 rows. Method of quoting only appears once though.

I don't understand why that code did not work for you then... it worked fine for me in the test I did before posting that code. However, since there is only one occurrence of that text in the column, this much simpler macro should work for you...

[table="width: 500"]
[tr]
	[td]Sub EnlargeMethodOfQuoting()
  Columns("C").Find("Method of Quoting:", , xlValues, xlWhole).RowHeight = 100
End Sub[/td]
[/tr]
[/table]