Need to macro to filter blanks and update text

[Revathi]

Hi all,

I need a macro with the following requirements.

1. I need to filter blanks in columns U and update those blanks as BRANCH.

2. I need to paste as blanks only for the used range in a column

 

[JackDanIce]

For 1, try:

Sub ReplaceMe()
Dim x as Long
x = Cells(Rows.Count, 21).End(xlUp).Row
On Error Resume Next
Range("U1:U" & x).SpecialCells(xlCellTypeBlanks).Value = "BRANCH"
On Error Goto o
End Sub

Do not understand what you're asking for 2. What column? Is it a formula that returns "" or blank and you want to replace it with the value?

Reading your post but cannot see your spreadsheet, it's not clear.

 

[Peter_SSs]

1. I need to filter blanks in columns U and update those blanks as BRANCH.

Try

Sub Fill_Blanks()
  On Error Resume Next
  Columns("U").SpecialCells(xlBlanks).Value = "BRANCH"
  On Error GoTo 0
End Sub

2. I need to paste as blanks only for the used range in a column

I don't know what this means. Can you spell it out in more detail?

 

[JackDanIce]

Hi Peter, with Columns("U"), does the code determine it's own last used row in that column i.e. row of last used cell? If so, was unaware, may try to use this more often, thanks.

 

[Revathi]

Hi Jack,

Thank you for the help. But i should take the used range based on Column A wherein i should filter only the blanks in column U and fill all the blanks as Branch until the line item used based on Column A. Please help.

 

[Revathi]

Hi Peter,

Thanks a lot. It works great.. but it is filling the data till the end of the page but i need to paste only as "Branch" based on the data used based on Column A.

 

[Peter_SSs]

Hi Peter,

Thanks a lot. It works great.. but it is filling the data till the end of the page but i need to paste only as "Branch" based on the data used based on Column A.

Change this line

Columns("U").Resize(Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlBlanks).Value = "BRANCH"

 

[Peter_SSs]

Hi Peter, with Columns("U"), does the code determine it's own last used row in that column i.e. row of last used cell? If so, was unaware, may try to use this more often, thanks.

As indicated by the OP's comments, it goes to the end of the UsedRange

 

[Revathi]

Thanks a lot. That works great....

 

[Peter_SSs]

Thanks a lot. That works great....

You're welcome. Thanks for letting us know.