VBA to Replace all with hidden rows & columns

[CDThe1]

Hello all,

I have a macro that will replace all within a workbook, but it won't replace if rows/columns are hidden. How can I make it so it will replace in hidden rows and columns? Thank you in advance

Sub FindReplaceAll()
'PURPOSE: Find & Replace text/values throughout entire workbook
sht As WorksheetDim
fnd As VariantDim
rplc As

Variantfnd = "April"
rplc = "May"
For Each sht In ActiveWorkbook.Worksheets
sht.Cells.Replace what:=fnd,
Replacement:=rplc, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _ SearchFormat:=False, ReplaceFormat:=False
Next sht
End Sub

 

[DanteAmor]

You have to add the SpecialCells(xlCellTypeVisible) property:

Sub FindReplaceAll()
  'PURPOSE: Find & Replace text/values throughout entire workbook
  Dim sht As Worksheet
  Dim fnd As String
  Dim rplc As String
  fnd = "April"
  rplc = "May"
  For Each sht In ActiveWorkbook.Worksheets
    sht.Cells.SpecialCells(xlCellTypeVisible).Replace what:=fnd, Replacement:=rplc, LookAt:=xlPart, _
      SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
  Next sht
End Sub

 

[CDThe1]

You have to add the SpecialCells(xlCellTypeVisible) property:

Sub FindReplaceAll()
  'PURPOSE: Find & Replace text/values throughout entire workbook
  Dim sht As Worksheet
  Dim fnd As String
  Dim rplc As String
  fnd = "April"
  rplc = "May"
  For Each sht In ActiveWorkbook.Worksheets
    sht.Cells.SpecialCells(xlCellTypeVisible).Replace what:=fnd, Replacement:=rplc, LookAt:=xlPart, _
      SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
  Next sht
End Sub

This works, but only on one sheet.

 

[DanteAmor]

but only on one sheet.

Works for me for all sheets.
Are the sheets visible?
Are the sheets unprotected?
Are you running the macro on the active workbook?

 

[CDThe1]

Works for me for all sheets.
Are the sheets visible?
Are the sheets unprotected?
Are you running the macro on the active workbook?

I have a workbook of about 40 worksheets. All are visible and none are protected. It runs on the first sheet and that's it. Takes less than 5 seconds.

 

[DanteAmor]

Are you sure that on sheets 2 onwards you have hidden rows and columns?
You say it only does it with the first page.
I need you to show me 3 images of the second sheet:
Before running the macro:

Image 1. With Hidden columns.​

Image 2. With the columns visible.​

Hide the columns.
After running the macro:

Image 3. With the columns visible.​

At this point, according to you, the macro should have replaced the values even of the data that was in the hidden column.

 

[DanteAmor]

Do you have more than one book open?

Is the book with the 40 pages the same one that contains the macro?

If so, try this:

Sub FindReplaceAll()
  'PURPOSE: Find & Replace text/values throughout entire workbook
  Dim sht As Worksheet
  Dim fnd As String
  Dim rplc As String
  fnd = "April"
  rplc = "May"
  For Each sht In ThisWorkbook.Worksheets
    sht.Cells.SpecialCells(xlCellTypeVisible).Replace what:=fnd, Replacement:=rplc, LookAt:=xlPart, _
      SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
  Next sht
End Sub

 

[CDThe1]

Do you have more than one book open?

Is the book with the 40 pages the same one that contains the macro?

If so, try this:

Sub FindReplaceAll()
  'PURPOSE: Find & Replace text/values throughout entire workbook
  Dim sht As Worksheet
  Dim fnd As String
  Dim rplc As String
  fnd = "April"
  rplc = "May"
  For Each sht In ThisWorkbook.Worksheets
    sht.Cells.SpecialCells(xlCellTypeVisible).Replace what:=fnd, Replacement:=rplc, LookAt:=xlPart, _
      SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
  Next sht
End Sub

This works, but only if the rows are unhidden first. If I unhide the rows on multiple sheets, the macro continues until it finds a sheet with the rows hidden.

 

[DanteAmor]

Try:

Sub FindReplaceAll()
  'PURPOSE: Find & Replace text/values throughout entire workbook
  Dim sht As Worksheet
  Dim fnd As String
  Dim rplc As String
  fnd = "April"
  rplc = "May"
  For Each sht In ThisWorkbook.Worksheets
    sht.Cells.Replace what:=fnd, Replacement:=rplc, LookAt:=xlPart, _
      SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
  Next sht
End Sub

 

[CDThe1]

Try:

Sub FindReplaceAll()
  'PURPOSE: Find & Replace text/values throughout entire workbook
  Dim sht As Worksheet
  Dim fnd As String
  Dim rplc As String
  fnd = "April"
  rplc = "May"
  For Each sht In ThisWorkbook.Worksheets
    sht.Cells.Replace what:=fnd, Replacement:=rplc, LookAt:=xlPart, _
      SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
  Next sht
End Sub

Same thing. Works on hidden in sheet one, but goes to sheet two and stops. Again, if I unhide the rows for several sheets in a row, it works but stops when it gets to the sheet with hidden rows.