Macros-count the last row & use the results

[lkcred]

I want to use the value of the last row found into a range using For Each...In [A:A]...in other words I want the range to stop at the last cell w/content in it...

'***Find last row
LastRow = Cells(Rows.Count, 1).End(xlUp).Row

'***Modify Data In Cells
For Each nb In [A:A] or LastRow.Rows
If nb.Value Like "*description *" Then
nb.Cells.Font.Bold = True
End If
Next nb

I want the loop to stop at the last row it found...then it moves to the next part of the code...

 

[DILIPandey]

Okay... update below in the code:-
For Each nb In Range("A1:A" & LastRow)

and try again.

Regards,
DILIPandey

 

[CharlesChuckieCharles]

Change lastrow to range

'***Find last row
set LastRow = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)

'***Modify Data In Cells
For Each nb In LastRow

If nb.Value Like "*description *" Then
nb.Cells.Font.Bold = True
End If
Next nb

 

[hiker95]

lkcred,

Welcome to the MrExcel forum.

What version of Excel and Windows are you using?

Sample raw data:

Excel 2007
	A
1	Title C
2	A2
3	My description of something
4	A4
5	A5
6	Your description of something else
7	A7
8	A8
9	A9
10	My description of something else
11	A11
12
Sheet1

After the macro:

Excel 2007
	A
1	Title C
2	A2
3	My description of something
4	A4
5	A5
6	Your description of something else
7	A7
8	A8
9	A9
10	My description of something else
11	A11
12
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub Test()
' hiker95, 02/19/2014, ME758634

Dim c As Range
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  If c Like "*description *" Then c.Font.Bold = True
Next c

End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the Test macro.

 

[lkcred]

Okay... update below in the code:-
For Each nb In Range("A1:A" & LastRow)

and try again.

Regards,
DILIPandey

DILIPandey,

Thank you...very precise working solution...it worked.
Thank you for your quick reply...

 

[lkcred]

lkcred,

Welcome to the MrExcel forum.

What version of Excel and Windows are you using?

Hiker95,

Excel 2007, thank you for the welcome!

Sample raw data:

Excel 2007

	A
Title C
A2
My description of something
A4
A5
Your description of something else
A7
A8
A9
My description of something else
A11

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

After the macro:

Excel 2007

	A
Title C
A2
My description of something
A4
A5
Your description of something else
A7
A8
A9
My description of something else
A11

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub Test()
' hiker95, 02/19/2014, ME758634

Dim c As Range
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  If c Like "*description *" Then c.Font.Bold = True
Next c

End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the Test macro.

I'm using 2007, thank you for the welcome...

 

[hiker95]

lkcred,

thank you for the welcome...

Did you try my macro?

 

[lkcred]

lkcred,



Did you try my macro?

Yes....it works a lot faster...thanks again...

 

[hiker95]

lkcred,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

 

[lkcred]

Hiker95,

I have another issue now...I'm trying to "c[FONT=Consolas, Menlo, Monaco, Lucida Console, Liberation Mono, DejaVu Sans Mono, Bitstream Vera Sans Mono, Courier New, monospace, serif]oncatenate" the contents of 2 cells.
1)needs to go through & look for an cell containing FE* or Vlan*
2)once the FE* or Vlan* are found then i want to "[/FONT][FONT=Consolas, Menlo, Monaco, Lucida Console, Liberation Mono, DejaVu Sans Mono, Bitstream Vera Sans Mono, Courier New, monospace, serif]merge" the contents of the found cell and the next cell below it...
3)moved to a new cell...

Here is what I have, it doesn't work:
[/FONT]'***combine data in each cell & move them to new location
For Each i In [A1:A4000]
If i.Value Like "FE*" Or i.Value Like "Vlan*" Then
Range("B1").Select
ActiveCell.FormulaR1C1 = "=RC[-1]&"" ""&R[1]C[-1]&"""""
End If
Next i