Find and Replace Within Range VBA

jmazorra

Well-known Member
Joined
Mar 19, 2011
Messages
707
Hello everyone:

I wrote a VBA code that would allow me to find and replace $ for Y I in column I. But also I need to replace blank cells in column I with N. The problem I have is that it fills all the blank cells down with N and I only need the cells within a range filled. In other words, there are 24 rows of data from I2 down, then search for Blanks and Replace with N in those 24 rows.

At the end of the code below you can see that I got to half my VBA, but I cannot figure out the replace blanks with N within range.

Any help will be appreciated

Private Sub CommandButton1_Click()

Sheets("byposition").Select
Rows("1:7").Select
Selection.Delete Shift:=xlUp
Columns("E:E").Select
Selection.Copy
Columns("A:A").Select
ActiveSheet.Paste
Cells.Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Columns("I:I").Select
Selection.Replace What:="$", Replacement:="Y", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
MsgBox "Step 1 Completed"
End Sub
 
Last edited:

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
Hi

Maybe something like

Code:
Private Sub CommandButton1_Click()
Dim rws As Long
With Sheets("byemployee")
    .Rows("1:7").Delete Shift:=xlUp
    .Cells.Sort Key1:=Range("A2"), Order1:=xlAscending
End With
With Sheets("byposition")
    rws = .UsedRange.Rows.Count
    .Rows("1:7").Delete Shift:=xlUp
    .Columns("E:E").Copy
    .Columns("A:A").PasteSpecial
    .Cells.Sort Key1:=Range("A2"), Order1:=xlAscending
    .Columns("I:I").Replace What:="$", Replacement:="Y"
    .Range("I2:I" & rws).SpecialCells(xlCellTypeBlanks).Value = "N"
End With
MsgBox "Step 1 Completed"
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,114,013
Messages
5,545,483
Members
410,685
Latest member
chandraganji
Top