VBA to insert word in visible cells only within filtered spreadsheet

[Surreybloke]

Hi,

I was wondering whether it's possible to have a macro that will insert a word (GLOBAL) in a particular column of blank cells within a filtered spreadsheet. Therefore, I obviously wouldn't want other cells that are not visible after applying the filter to also have the word added.

Many thanks.

 

[Jon von der Heyden]

Hi

Lets say you are dealing with A1:A100, then:

Range("A1:A100").SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeBlanks).Value="GLOBAL"

 

[Surreybloke]

Thanks for your help - it would also need to be for a specific worksheet as well as there are multiple ones in the spreadsheet.

So I guess if I were to add something like this before your code above, then that would fit the bill?

' Set to run on "Data" sheet
Set ws = Sheets("Invoice File")

Just as an extra note, the number of rows will be a variable amount so rather than have a fixed figure, I would need to begin from row 3 each time, and terminate as and when it reaches the final row of data.

I was provided with the following code as part of a previous question, so I guess this could be incorporated into your code to fulfill what I need?

' Find last row in column DT with data
lastRow = ws.Cells(Rows.Count, "DT").End(xlUp).Row

' Set range to blank cells in column DK
Set rng = ws.Range("DK3:DK" & lastRow).SpecialCells(xlCellTypeBlanks)

 

[Jon von der Heyden]

Hi

Demo for sheet with CodeName Sheet1, using an intersection of the column and the UsedRange:

    With Sheet1
        Application.Intersect(.Range("A:A"), .UsedRange).SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeBlanks).Value = "GLOBAL"
    End With

Demo for sheet with CodeName Sheet1, using LastRow long type variable:

    With Sheet1
        lngLastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("A1:A" & lngLastRow).SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeBlanks).Value = "GLOBAL"
    End With

 

[Surreybloke]

The name of the worksheet is Invoice File, I've tried replacing Sheet1 with Invoice File, but it didn't like the syntax.

As I put in my previous reply, do you think this code would work within the overall code you've suggested?

Set ws = Sheets("Invoice File")

That didn't work so hopefully you can modify the code to make this fit?

 

[Jon von der Heyden]

Does this work?

    Dim ws As Excel.Worksheet
    Dim lngLastRow as Long

    Set ws = Sheets("Invoice File")
    
     With ws
        lngLastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("A1:A" & lngLastRow).SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeBlanks).Value = "GLOBAL"
    End With

 

[Surreybloke]

I've found just entering the code as above means it isn't listed as an option when running a macro?

I amended it slightly as below, but it's getting stuck on the first line when I run it.

Sub SetGlobal()


Dim ws As Excel.Worksheet
Dim lngLastRow As Long


Set ws = Sheets("Invoice File")

With ws
lngLastRow = .Range("F" & .Rows.Count).End(xlUp).Row
.Range("F1:F" & lngLastRow).SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeBlanks).Value = "GLOBAL"
End With

 

[Jon von der Heyden]

Try;

Sub SetGlobal()
    Dim ws As Excel.Worksheet
    Dim lngLastRow as Long


    Set ws = Sheets("Invoice File")
    
     With ws
        lngLastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("A1:A" & lngLastRow).SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeBlanks).Value = "GLOBAL"
    End With
End Sub

 

[Surreybloke]

It doesn't like this section of code for some reason?

.Range("F1:F" & lngLastRow).SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeBlanks).Value = "GLOBAL"

It comes up with the debug window each time.

 

[Surreybloke]

Unfortunately the debugger doesn't really illuminate any further as to why it doesn't like that section of code, so hope you might have an idea why it might not be accepting it?