VBA Script last cell/column with remove replace copy formatting

[lefty38]

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



[TABLE="width: 842"]
<tbody>[TR]
[/TR]
</tbody>[/TABLE]

<tbody>
[TD="class: xl119"]
[/TD]
[TD="class: xl119, width: 64"]
[/TD]
[TD="class: xl119, width: 64"]
[/TD]
[TD="class: xl119, width: 64"]
[/TD]
[TD="class: xl119, width: 64"]
[/TD]
[TD="class: xl119, width: 64"]
[/TD]
[TD="class: xl119, width: 64"]
[/TD]
[TD="class: xl145, width: 62"] train 1 [/TD]
[TD="class: xl145, width: 76"] train 2 [/TD]
[TD="class: xl145, width: 64"] train 3 [/TD]
[TD="class: xl145, width: 64"] train 4 [/TD]
[TD="class: xl145, width: 64"] train 5 [/TD]
[TD="class: xl145, width: 64"] train 6 [/TD]

[TD="class: xl119"]
[/TD]
[TD="class: xl119"]
[/TD]
[TD="class: xl119"]
[/TD]
[TD="class: xl119"]
[/TD]
[TD="class: xl119"]
[/TD]
[TD="class: xl119"]
[/TD]
[TD="class: xl119"]
[/TD]
[TD="class: xl145, width: 62"] ABC123 [/TD]
[TD="class: xl145, width: 76"] ABC124 [/TD]
[TD="class: xl145, width: 64"] ABC125 [/TD]
[TD="class: xl145, width: 64"] ABC126 [/TD]
[TD="class: xl145, width: 64"] ABC127 [/TD]
[TD="class: xl145, width: 64"] ABC128 [/TD]

[TD="class: xl223"] Division [/TD]
[TD="class: xl223"] data [/TD]
[TD="class: xl223"] more data [/TD]
[TD="class: xl223"] Manager [/TD]
[TD="class: xl223"] Employee [/TD]
[TD="class: xl223"] Emp Id [/TD]
[TD="class: xl223"] Pct Comp [/TD]
[TD="class: xl144"] Push [/TD]
[TD="class: xl144"] Push [/TD]
[TD="class: xl119"] Question [/TD]
[TD="class: xl144"] Push [/TD]
[TD="class: xl144"] Push [/TD]
[TD="class: xl119"] Question [/TD]

[TD="class: xl216"] hr [/TD]
[TD="class: xl217"] west [/TD]
[TD="class: xl217"] north [/TD]
[TD="class: xl217"] Fred [/TD]
[TD="class: xl217"] emp1 [/TD]
[TD="class: xl148, align: right"] 11 [/TD]
[TD="class: xl221, align: right"] 89.5%
[/TD]
[TD="class: xl121"] NR
[/TD]
[TD="class: xl121"] Complete [/TD]
[TD="class: xl121"]
[/TD]
[TD="class: xl121"] Complete [/TD]
[TD="class: xl121"]
[/TD]
[TD="class: xl121"] Complete [/TD]

[TD="class: xl216"] hr [/TD]
[TD="class: xl217"] west [/TD]
[TD="class: xl217"] north [/TD]
[TD="class: xl217"] wilma [/TD]
[TD="class: xl217"] emp2 [/TD]
[TD="class: xl148, align: right"] 12 [/TD]
[TD="class: xl221, align: right"] 96.9% [/TD]
[TD="class: xl108"] NR
[/TD]
[TD="class: xl108"] Complete [/TD]
[TD="class: xl108"]
[/TD]
[TD="class: xl108"] Complete [/TD]
[TD="class: xl108"] Complete [/TD]
[TD="class: xl108"]
[/TD]

[TD="class: xl216"] hr [/TD]
[TD="class: xl217"] coast [/TD]
[TD="class: xl217"] north [/TD]
[TD="class: xl217"] barney [/TD]
[TD="class: xl217"] emp3 [/TD]
[TD="class: xl148, align: right"] 13 [/TD]
[TD="class: xl221, align: right"] 96.4% [/TD]
[TD="class: xl108"]
[/TD]
[TD="class: xl108"] Complete [/TD]
[TD="class: xl108"]
[/TD]
[TD="class: xl108"] Complete [/TD]
[TD="class: xl108"] Complete [/TD]
[TD="class: xl108"]
[/TD]

[TD="class: xl216"] hr [/TD]
[TD="class: xl217"] west [/TD]
[TD="class: xl217"] north [/TD]
[TD="class: xl217"] Fred [/TD]
[TD="class: xl217"] emp4 [/TD]
[TD="class: xl148, align: right"] 14 [/TD]
[TD="class: xl221, align: right"] 100.0% [/TD]
[TD="class: xl108"]
[/TD]
[TD="class: xl108"] Complete [/TD]
[TD="class: xl108"]
[/TD]
[TD="class: xl108"] Complete [/TD]
[TD="class: xl108"] Complete [/TD]
[TD="class: xl108"]
[/TD]

[TD="class: xl216"] hr [/TD]
[TD="class: xl217"] west [/TD]
[TD="class: xl217"] north [/TD]
[TD="class: xl217"] wilma [/TD]
[TD="class: xl217"] emp5 [/TD]
[TD="class: xl148, align: right"] 15 [/TD]
[TD="class: xl221, align: right"] 100.0% [/TD]
[TD="class: xl108"]
[/TD]
[TD="class: xl108"]
[/TD]
[TD="class: xl108"]
[/TD]
[TD="class: xl108"] Complete [/TD]
[TD="class: xl108"] Complete [/TD]
[TD="class: xl108"]
[/TD]

[TD="class: xl216"] hr [/TD]
[TD="class: xl217"] west [/TD]
[TD="class: xl217"] north [/TD]
[TD="class: xl217"] barney [/TD]
[TD="class: xl217"] emp6 [/TD]
[TD="class: xl148, align: right"] 16 [/TD]
[TD="class: xl221, align: right"] 100.0% [/TD]
[TD="class: xl108"]
[/TD]
[TD="class: xl108"]
[/TD]
[TD="class: xl108"]
[/TD]
[TD="class: xl108"] Complete [/TD]
[TD="class: xl108"] Complete [/TD]
[TD="class: xl108"]
[/TD]

[TD="class: xl216"] hr [/TD]
[TD="class: xl217"] west [/TD]
[TD="class: xl217"] north [/TD]
[TD="class: xl217"] Fred [/TD]
[TD="class: xl217"] emp7 [/TD]
[TD="class: xl148, align: right"] 17 [/TD]
[TD="class: xl221, align: right"] 58.8% [/TD]
[TD="class: xl108"]
[/TD]
[TD="class: xl108"]
[/TD]
[TD="class: xl108"]
[/TD]
[TD="class: xl108"] Complete [/TD]
[TD="class: xl108"] 07/31/17 [/TD]
[TD="class: xl108"]
[/TD]

</tbody>

 

[Takae]

Hope this helps.

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]

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]

Sorry it was crude.
Assuming columnA does not have blank cell.

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]

a little bit fast?

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

 

[lefty38]

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)

[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]

Here's another option

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]

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"?

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]

Select one of the cells that contain the hidden character & run this

Sub chk()
MsgBox Asc(activecell)
End Sub

what does the msgbox say?

 

[lefty38]

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)