Suppose this is my initial excel sheet
I want to replace all the non-empty cells in column C with the string "Title" excluding the column header.
The output should be like this:
Thank you!
-
Both image is same. Did you try anything? Show us what you tried already.Harun24hr– Harun24hr2021-02-18 05:40:56 +00:00Commented Feb 18, 2021 at 5:40
-
My apology. You can check it again.Div– Div2021-02-18 05:43:02 +00:00Commented Feb 18, 2021 at 5:43
Add a comment
|
4 Answers
Try below sub.
Sub FillTitle()
Dim lrow As Long
Dim rng As Range
lrow = Cells(Rows.Count, "C").End(xlUp).Row 'Detect last data entry row in Column C.
For Each rng In Range("C2:C" & lrow)
If rng <> "" Then
rng = "Title"
End If
Next rng
End Sub
Comments
Range has Replace method:
Sub ReplaceAll()
With ActiveSheet
Application.Intersect(.UsedRange, .UsedRange.Offset(1), .Columns("C")).Replace What:="*", Replacement:="Tester"
' reset Find/Replace pattern to default for further use
.Cells.Find What:="", LookIn:=xlFormulas, SearchOrder:=xlRows, LookAt:=xlPart, MatchCase:=False
End With
End Sub
Comments
For small table, just run a for loop:
for row=2 to something_large
if cells(row,col)<>"" then cells(row,col)="title"
next row
for large table, your best bet is to record a macro: create a filter on the column, filter to nonblank, select all rows, then paste to them. Once you have the macro, modify for general use.
1 Comment
Div
I'll take this method into consideration for other processes. Thank you!
You can choose not use loop as well by using a simple code line like below which will perform the same action.
Range("C2:C" & Range("B" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeConstants).Value = "Title"
