VBA Script last cell/column with remove replace copy formatting

lefty38

Board Regular
Joined
Oct 27, 2005
Messages
85
Hello - this site has always been a great source of help
once again
i am looking for VB code that will select from cell H4 out to the last column/last cell
then with that selection perform two functions
find null values and replace with NR
copy the conditional formatting of H4 and paint brush to the last cell / column
excel version 2013
cells can contain null values
employee id will always have a value
again thank you




<tbody>
</tbody>







train 1train 2train 3train 4train 5train 6







ABC123ABC124ABC125ABC126ABC127ABC128
Divisiondatamore dataManagerEmployeeEmp IdPct CompPushPushQuestionPushPushQuestion
hrwestnorthFredemp11189.5%
NR
Complete
Complete
Complete
hrwest northwilmaemp21296.9%NR
Complete
CompleteComplete
hrcoastnorthbarneyemp31396.4%
Complete
CompleteComplete
hrwest northFredemp414100.0%
Complete
CompleteComplete
hrwest northwilmaemp515100.0%


CompleteComplete
hrwest northbarneyemp616100.0%


CompleteComplete
hrwest northFredemp71758.8%


Complete07/31/17

<tbody>
</tbody>
 

Takae

Well-known Member
Joined
Jul 1, 2015
Messages
674
Hope this helps.

Code:
Sub test()
    Dim rng As Range, c
    Set rng = Range(Range("H4"), Range("H4").SpecialCells(xlLastCell))
    For Each c In rng
        If c = "" Then
            c.Value = "NR"
        End If
    Next
    Range("H4").copy
    rng.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub
 

lefty38

Board Regular
Joined
Oct 27, 2005
Messages
85
Takea,
the code works well and does not go past the last column of data,
but for some reason on the row data it goes further than the last row that has data (8,000+ rows receive "NR" values)
is there a way to stop the script at the last row that has data in it?
 

Takae

Well-known Member
Joined
Jul 1, 2015
Messages
674
Sorry it was crude.
Assuming columnA does not have blank cell.

Code:
Sub test()
    Dim rng As Range, c
    Dim LR As Long, LC As Long
    LR = cells(Rows.count, 1).End(xlUp).Row
    LC = cells(1, Columns.count).End(xlToLeft).Column
    Set rng = Range(Range("H4"), cells(LR, LC))
    For Each c In rng
        If c = "" Then
            c.Value = "NR"
        End If
    Next
    Range("H4").copy
    rng.PasteSpecial Paste:=xlPasteFormats
End Sub
 

Takae

Well-known Member
Joined
Jul 1, 2015
Messages
674
a little bit fast?

Code:
Sub test()
    Dim rng, c
    Dim LR As Long, LC As Long, i As Long, j As Long
    Dim x
    LR = cells(Rows.count, 1).End(xlUp).Row
    LC = cells(1, Columns.count).End(xlToLeft).Column
    rng = Range(Range("H4"), cells(LR, LC))
    For i = 1 To LC - 7
        For j = 1 To LR - 3
            If rng(j, i) = "" Then
                rng(j, i) = "NR"
            End If
        Next
    Next
    Range(Range("H4"), cells(LR, LC)) = rng
    Range("H4").copy
    Range(Range("H4"), cells(LR, LC)).PasteSpecial Paste:=xlPasteFormats
End Sub
 
Last edited:

lefty38

Board Regular
Joined
Oct 27, 2005
Messages
85
Takae, my apologies,
I thought I replied on the results of your helpfulness
the script works 99%
but does not edit the last row & last column --the script selects (H2443) and column (AP:) but does not find "" & replace with the "NR"

(I have modified the starting point as (H6)

Any suggestions?
---------------------------------------------------------------------------------------------------

info in locals window
LR=2443 rows (long)
LC=42 columns (long)


Code:
[Sub RR_NR_Format()
    Dim rng, c
    Dim LR As Long, LC As Long, i As Long, j As Long
    Dim x
    LR = Cells(Rows.Count, 1).End(xlUp).Row
    LC = Cells(1, Columns.Count).End(xlToLeft).Column
    rng = Range(Range("H6"), Cells(LR, LC))
    For i = 1 To LC - 8
        For j = 1 To LR - 6
            If rng(j, i) = "" Then
               rng(j, i) = "NR"
            End If
        Next
    Next
    Range(Range("H6"), Cells(LR, LC)) = rng
    Range("H6").Copy
    Range(Range("H6"), Cells(LR, LC)).PasteSpecial Paste:=xlPasteFormats
End Sub/CODE]
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
31,974
Office Version
365
Platform
Windows
Here's another option
Code:
Sub test()

    Dim Rng As Range
    Dim UsdCols As Long
    Dim UsdRws As Long

    UsdRws = Cells.Find("*", LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    UsdCols = Cells.Find("*", LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    
    Set Rng = Range("H4", Cells(UsdRws, UsdCols))
    Rng.SpecialCells(xlBlanks).Value = "NR"
    Range("H4").Copy
    Rng.PasteSpecial Paste:=xlPasteFormats
End Sub
 

lefty38

Board Regular
Joined
Oct 27, 2005
Messages
85
Fluff & All
thank you the last script worked fine - until my data source changed to Access crosstab
Now the blank cells are not really blank cells - they have a hidden character in them

I can double click on each one, then run the code to work - but double clicking on each cell is impractical
if I do not do anything the code errors runt time error '1004' No cells were found

what do I need to change to find the hidden character and replace it with 'NR"?
Code:
Sub test()

    Dim Rng As Range
    Dim UsdCols As Long
    Dim UsdRws As Long

    UsdRws = Cells.Find("*", LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    UsdCols = Cells.Find("*", LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    
    Set Rng = Range("H4", Cells(UsdRws, UsdCols))
    Rng.SpecialCells(xlBlanks).Value = "NR"
    Range("H4").Copy
    Rng.PasteSpecial Paste:=xlPasteFormats
End Sub

and thank you for your replies
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
31,974
Office Version
365
Platform
Windows
Select one of the cells that contain the hidden character & run this
Code:
Sub chk()
MsgBox Asc(activecell)
End Sub
what does the msgbox say?
 

lefty38

Board Regular
Joined
Oct 27, 2005
Messages
85
thanks Fluff
I selected the cell - it returned the error
Run-time Error '5'
Invalid procedure call or argument

where this line was highlighted in yellow ==> MsgBox Asc(ActiveCell)
 

Forum statistics

Threads
1,081,513
Messages
5,359,226
Members
400,521
Latest member
smarty1995

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top