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...

 

[CharlesChuckieCharles]

Sub TryThis()
Set LastRowRange = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)

' be aware that the components of the for statement are re-evaluated every interation
' so fix the variables of a for next loop if you can

For Each I In LastRowRange
    If I.Value Like "FE*" Or I.Value Like "Vlan*" Then
        I.Offset(0, 1) = I.Value & " " * I.Offset(1, 0).Value  ' merge current cell and next cell down into same row, one col across
        I.Value = ""  'clear current cell value
        I.Offset(1, 0).Value = "" 'clear next cell down to affect a MOVE
    End If
Next I
End Sub

 

[Rick Rothstein]

 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

For those who may be interested, there is a way to do this without looping through each cell in the range...

Sub BoldCellsWithDescriptionInIt()
  Application.ReplaceFormat.Clear
  Application.ReplaceFormat.Font.Bold = True
  Range("A1", Cells(Rows.Count, "A").End(xlUp)).Replace "description ", "description ", xlPart, , False, , False, True
  Application.ReplaceFormat.Clear
End Sub

 

[hiker95]

lkcred,

Please do not quote entire replies from your helper. When quoting follow these guidelines:
1. Quote ONLY if it is needed to add clarity or context for your reply. If so, then
2. Quote ONLY the specific part of the post that is relevant - - not the entire post.

This will keep thread clutter to a minimum and make the discussion easier to follow.

And, when you respond to your helper, please use their site ID/username/handle.


In the future when asking for help it would help if you supplied screenshots of before and after.

Sample raw data:

Excel 2007
	A	B
1	A1
2	A2
3	FEa3
4	A4
5	A5
6	VlanA6
7	A7
8	A8
9
Sheet1

After the new macro:

Excel 2007
	A	B
1	A1
2	A2
3	FEa3	FEa3 A4
4	A4
5	A5
6	VlanA6	VlanA6 A7
7	A7
8	A8
9
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 MergeCells()
' hiker95, 02/20/2014, ME758634
Application.ScreenUpdating = False
Dim c As Range
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  If c Like "FE*" Or c Like "Vlan*" Then
    With c.Offset(, 1)
      .FormulaR1C1 = "=RC[-1]&"" ""&R[1]C[-1]&"""""
      .Value = .Value
    End With
  End If
Next c
Columns(2).AutoFit
Application.ScreenUpdating = True
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 MergeCells macro.

 

[Rick Rothstein]

I have another issue now...I'm trying to "concatenate" 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 "merge" the contents of the found cell and the next cell below it...
3)moved to a new cell...

Here is another (non-looping) macro for you to consider...

Sub CombineFEorVlanCellsInColumnAintoColumnB()
  With Range("A1", Cells(Rows.Count, "A").End(xlUp)).Offset(, 1)
    .Formula = "=IF(OR(LEFT(RC1,2)=""FE"",LEFT(RC1,4)=""Vlan""),RC1&"" ""&R[1]C1,"""")"
    .Value = .Value
  End With
End Sub

 

[lkcred]

CharlesChuckieCharles

Thanks for the input...I tried your script, but it don't do anything...I must have left something out...

 

[Rick Rothstein]

CharlesChuckieCharles

Thanks for the input...I tried your script, but it don't do anything...I must have left something out...

Just out of curiosity, have you looked at any of the other solutions besides the one CharlesChuckieCharles posted?

Message #13 - A solution for your second question from hiker95

Message #14 - A solution for your second question from me.

And, as an extra....

Message #12 - An alternate solution for your first queston from me

 

[lkcred]

This script in #13 didnt give back any results....

 

[lkcred]

This script in #14 didnt give back any results....

 

[lkcred]

Script #12 worked...I replaced it w/a previous script.

 

[Rick Rothstein]

This script in #14 didnt give back any results....

What cells are your data in?