Selecting Specific Range for Cells in Find Replace

    Respected forum members

    I am doing Find replace using VBA, i am able to achieve results for entire sheet and multiple sheets but i am not able to select the right formula for find and replace in specific rows

    My requirement is to select from Row2 from cell A2 and go till Nth(last) column of last row and do find replace, I dont want to touch row1

    Code
    'Stored sheet to object
  Set mysheet = Sheets("Sheet1")

'Please suggest how can i select specific range from Row2 till end of rows for filled columns only  
  mysheet.Cells.Replace what:=fnd, Replacement:=rplc, _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False

    Option i tried and it failed for Class Range then 1004 object error, Kindly share your input

    Code
    mysheet.Rows(2 &":" & mysheet.cells(Rows.Count, "A").End(xlup).Row).Select 
                                                                                 ' i also used used range but dint work  
Selection.Cells.Replace what:=fnd, Replacement:=rplc, _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False
    Code
    mysheet.Rows(2 & ":" & mysheet.cells(Rows.Count, "A").End(xlup).Row).Cells.Replace what:=fnd, Replacement:=rplc, _
 LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
 SearchFormat:=False, ReplaceFormat:=False

    Just remove the Select, which is almost always redundant and inefficient anyway.

    If the data is in a Table format with a header row starting in A! then use CurrentRegion


    Code
    Dim oWs As Worksheet
Dim rRng As Range

Set oWs = Sheet1
Set rRng = oWs.Cells(2, 1).CurrentRegion

rRng.Offset(1, 0).Resize(rRng.Rows.Count - 1, _
rRng.Columns.Count).Replace what:=fnd, Replacement:=rplc, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login