Macros-count the last row & use the results

L

lkcred

New Member
Joined
Feb 19, 2014
Messages
23
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...
 
Sort by date Sort by votes
Thanks again, that partly work...the merged data are in cells scattered throughout the sheet.

I keep getting new requirements...so I'm going to have to re-think my approach...ill keep u posted.
 
Upvote 0

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
i have another is...the code below is to find specific "key words" & then line each in col B through K starting a row 3 for each, but it doesnt do it correctly...each col cascades downward. Also is there another way to shorten the code that does the same thing...thanks.

'***move content to col B through K
rw = 0
kb = 3
kc = 3
kd = 3
ke = 3
kf = 3
kg = 3
kh = 3
ki = 3
kj = 3
kk = 3
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
If c.Value Like "FE1*" Then
Range("B" & kb).Value = c.Value
Range("B" & kb).Interior.ColorIndex = 43
ElseIf c.Value Like "GE1*" Then
Range("B" & kb).Value = c.Value
Range("B" & kb).Interior.ColorIndex = 3
ElseIf c.Value Like "FE2*" Then
Range("C" & kc).Value = c.Value
Range("C" & kc).Interior.ColorIndex = 43
ElseIf c.Value Like "GE2*" Then
Range("C" & kc).Value = c.Value
Range("C" & kc).Interior.ColorIndex = 3
ElseIf c.Value Like "FE3*" Then
Range("D" & kd).Value = c.Value
Range("D" & kd).Interior.ColorIndex = 43
ElseIf c.Value Like "GE3*" Then
Range("D" & kd).Value = c.Value
Range("D" & kd).Interior.ColorIndex = 3
ElseIf c.Value Like "FE4*" Then
Range("E" & ke).Value = c.Value
Range("E" & ke).Interior.ColorIndex = 43
ElseIf c.Value Like "GE4*" Then
Range("E" & ke).Value = c.Value
Range("E" & ke).Interior.ColorIndex = 3
ElseIf c.Value Like "FE5*" Then
Range("F" & kf).Value = c.Value
Range("F" & kf).Interior.ColorIndex = 43
ElseIf c.Value Like "GE5*" Then
Range("F" & kf).Value = c.Value
Range("F" & kf).Interior.ColorIndex = 3
ElseIf c.Value Like "FE6*" Then
Range("G" & kg).Value = c.Value
Range("G" & kg).Interior.ColorIndex = 43
ElseIf c.Value Like "GE6*" Then
Range("G" & kg).Value = c.Value
Range("G" & kg).Interior.ColorIndex = 3
ElseIf c.Value Like "FE7*" Then
Range("H" & kh).Value = c.Value
Range("H" & kh).Interior.ColorIndex = 43
ElseIf c.Value Like "GE7*" Then
Range("H" & kh).Value = c.Value
Range("H" & kh).Interior.ColorIndex = 3
ElseIf c.Value Like "FE8*" Then
Range("I" & ki).Value = c.Value
Range("I" & ki).Interior.ColorIndex = 43
ElseIf c.Value Like "GE8*" Then
Range("I" & ki).Value = c.Value
Range("I" & ki).Interior.ColorIndex = 3
ElseIf c.Value Like "FE9*" Then
Range("J" & kj).Value = c.Value
Range("J" & kj).Interior.ColorIndex = 43
ElseIf c.Value Like "GE9*" Then
Range("J" & kj).Value = c.Value
Range("J" & kj).Interior.ColorIndex = 3
ElseIf c.Value Like "Vl*" Then
Range("K" & kk).Value = c.Value
End If
kb = kb + 1
kc = kc + 1
kd = kd + 1
ke = ke + 1
kf = kf + 1
kg = kg + 1
kh = kh + 1
ki = ki + 1
kj = kj + 1
kk = kk + 1
Next c
 
Upvote 0
yes, let me try...the input is a network configuration file, the code is suppose to extract & merge found data and then separate each cell into multiple col's...

Error Output:
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 56"]
<colgroup><col></colgroup><tbody>[TR]
[TD]GE1/0/1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GE1/0/2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GE1/0/3
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]FE2/0/1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]FE2/0/2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]FE2/0/3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]FE3/0/1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]FE3/0/2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]FE3/0/3[/TD]
[/TR]
</tbody>[/TABLE]



Output should be:
[TABLE="width: 500"]
<tbody>[TR]
[TD]GE1/0/1[/TD]
[TD]FE2/0/1[/TD]
[TD]FE3/0/1[/TD]
[/TR]
[TR]
[TD]GE1/0/2[/TD]
[TD]FE2/0/2[/TD]
[TD]FE3/0/2[/TD]
[/TR]
[TR]
[TD]GE1/0/3[/TD]
[TD]FE2/0/3[/TD]
[TD]FE3/0/3[/TD]
[/TR]
</tbody>[/TABLE]


Here is the full code:

Dim Filter As String, Title As String, FilterIndex As Integer, Filename As Variant, LastRow As Long
Dim rng As Range, c As Range, nb As Range, x As String, varLastRow, rw As Long, a As Range, b As Range, ra As Long, rb As Long
'***************************************************Function To Get Information From A File
Sub MacrosCFG()
'***File types
Filter = "Excel Files (*.xls),*.xls," & _
"Text Files (*.txt),*.txt," & _
"All Files (*.*),*.*"
FilterIndex = 3 'Default Filter to *.*
Title = "SELECT FILE TO OPEN:" 'Caption

'*************************************Start Drive & Path
ChDir ("C:\")
With Application
'***Name selected File
Filename = .GetOpenFilename(Filter, FilterIndex, Title, ActiveWorkbook.Name)
Workbooks.Open Filename, ReadOnly:=True
End With
'***Exit With No Selection
If Filename = False Then
MsgBox "NO FILE WAS SELECTED!", vbExclamation
Exit Sub
End If
'***Open File & Extract Data
'Workbooks.Open Filename, ReadOnly:=True
' If filename = true than
' MsgBox Filename, vbInformation, "DATA EXTRACTED FROM:"
'If filename = false than

'**************************************Modify Data In Cells
'***replace wanted content in cells
Application.ReplaceFormat.Clear
Range("A1", Cells(Rows.Count, "A").End(xlUp)).Replace "hostname ", "", xlPart, , False, , False, True
Range("A1", Cells(Rows.Count, "A").End(xlUp)).Replace "description ", "", xlPart, , False, , False, True
Range("A1", Cells(Rows.Count, "A").End(xlUp)).Replace "interface ", "", xlPart, , False, , False, True
Range("A1", Cells(Rows.Count, "A").End(xlUp)).Replace "switchport access vlan ", "", xlPart, , False, , False, True
Range("A1", Cells(Rows.Count, "A").End(xlUp)).Replace "*ip address", "", xlPart, , False, , False, True
Range("A1", Cells(Rows.Count, "A").End(xlUp)).Replace "GigabitEthernet", "GE", xlPart, , False, , False, True
Range("A1", Cells(Rows.Count, "A").End(xlUp)).Replace "FastEthernet", "FE", xlPart, , False, , False, True
Application.ReplaceFormat.Clear

'***combine wanted content of 3 cells in a row
rw = 1
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
If c.Value Like "*FE*" Or c.Value Like "*GE*" Then
Range("B" & rw).Value = c.Value & " " & c.Offset(1).Value & " (Vlan" & c.Offset(2).Value & ")"
'ElseIf c.Value Like "Vlan*" Then
' Range("B" & rw).Value = c.Value & " " & c.Offset(1).Value & " " & c.Offset(2).Value
rw = rw + 1
End If
Next c

[A:A].Delete

'********************************************seperate cells into 48 cell sets
'***move content to col B
rw = 0
kb = 3
kc = 3
kd = 3
ke = 3
kf = 3
kg = 3
kh = 3
ki = 3
kj = 3
kk = 3
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
If c.Value Like "FE1*" Then
Range("B" & kb).Value = c.Value
Range("B" & kb).Interior.ColorIndex = 43
ElseIf c.Value Like "GE1*" Then
Range("B" & kb).Value = c.Value
Range("B" & kb).Interior.ColorIndex = 3
ElseIf c.Value Like "FE2*" Then
Range("C" & kc).Value = c.Value
Range("C" & kc).Interior.ColorIndex = 43
ElseIf c.Value Like "GE2*" Then
Range("C" & kc).Value = c.Value
Range("C" & kc).Interior.ColorIndex = 3
ElseIf c.Value Like "FE3*" Then
Range("D" & kd).Value = c.Value
Range("D" & kd).Interior.ColorIndex = 43
ElseIf c.Value Like "GE3*" Then
Range("D" & kd).Value = c.Value
Range("D" & kd).Interior.ColorIndex = 3
ElseIf c.Value Like "FE4*" Then
Range("E" & ke).Value = c.Value
Range("E" & ke).Interior.ColorIndex = 43
ElseIf c.Value Like "GE4*" Then
Range("E" & ke).Value = c.Value
Range("E" & ke).Interior.ColorIndex = 3
ElseIf c.Value Like "FE5*" Then
Range("F" & kf).Value = c.Value
Range("F" & kf).Interior.ColorIndex = 43
ElseIf c.Value Like "GE5*" Then
Range("F" & kf).Value = c.Value
Range("F" & kf).Interior.ColorIndex = 3
ElseIf c.Value Like "FE6*" Then
Range("G" & kg).Value = c.Value
Range("G" & kg).Interior.ColorIndex = 43
ElseIf c.Value Like "GE6*" Then
Range("G" & kg).Value = c.Value
Range("G" & kg).Interior.ColorIndex = 3
ElseIf c.Value Like "FE7*" Then
Range("H" & kh).Value = c.Value
Range("H" & kh).Interior.ColorIndex = 43
ElseIf c.Value Like "GE7*" Then
Range("H" & kh).Value = c.Value
Range("H" & kh).Interior.ColorIndex = 3
ElseIf c.Value Like "FE8*" Then
Range("I" & ki).Value = c.Value
Range("I" & ki).Interior.ColorIndex = 43
ElseIf c.Value Like "GE8*" Then
Range("I" & ki).Value = c.Value
Range("I" & ki).Interior.ColorIndex = 3
ElseIf c.Value Like "FE9*" Then
Range("J" & kj).Value = c.Value
Range("J" & kj).Interior.ColorIndex = 43
ElseIf c.Value Like "GE9*" Then
Range("J" & kj).Value = c.Value
Range("J" & kj).Interior.ColorIndex = 3
ElseIf c.Value Like "Vl*" Then
Range("K" & kk).Value = c.Value
End If
kb = kb + 1
kc = kc + 1
kd = kd + 1
ke = ke + 1
kf = kf + 1
kg = kg + 1
kh = kh + 1
ki = ki + 1
kj = kj + 1
kk = kk + 1
Next c

'***clear content in col A
Range("A1", Range("A" & Rows.Count).End(xlUp)).Select
Selection.ClearContents
Columns("A:Z").EntireColumn.AutoFit
Range("A1").Select
End Sub
 
Upvote 0
lkcred said:
yes, let me try...the input is a network configuration file, the code is suppose to extract & merge found data and then separate each cell into multiple col's...

Error Output:
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD]GE1/0/1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GE1/0/2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GE1/0/3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]FE2/0/1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]FE2/0/2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]FE2/0/3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]FE3/0/1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]FE3/0/2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]FE3/0/3[/TD]
[/TR]
</tbody>[/TABLE]



Output should be:
[TABLE="width: 500"]
<tbody>[TR]
[TD]GE1/0/1[/TD]
[TD]FE2/0/1[/TD]
[TD]FE3/0/1[/TD]
[/TR]
[TR]
[TD]GE1/0/2[/TD]
[TD]FE2/0/2[/TD]
[TD]FE3/0/2[/TD]
[/TR]
[TR]
[TD]GE1/0/3[/TD]
[TD]FE2/0/3[/TD]
[TD]FE3/0/3[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
It looks like you are doing more than just that in your code (for example, I see stuff about colors in there), but collapsing the data from multiple rows shown in the "Error Output" to non-spaced rows as shown in the "Output should be" can be done in one line of code...

Columns("A:C").SpecialCells(xlBlanks).Delete xlShiftUp
 
Upvote 0
yah...i have a lot of other stuff i left out of the example...thanks for the line of code...that worked...
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
setting a range from Active cell to last cell when Active cell change columns
2
Replies
12
Views
464
jolivanes
jolivanes
J
  • Question Question
VBA Search box and button code
Replies
0
Views
199
jlcodes
J
M
  • Solved
cut/copy and paste row based on cell value not being equal to specific text
Replies
6
Views
415
mlan1604
M
M
  • Solved
vba to sum up each column for each employee on column (A)
Replies
6
Views
299
kvsrinivasamurthy
K
M
  • Solved
run formula based on cell value to the last row
Replies
5
Views
341
MMM_84
M

Forum statistics

Threads
1,223,247
Messages
6,171,007
Members
452,374
Latest member
keccles

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top