VBA: Give a value to blank cells in rows that meet certain criteria

Question

Well I have done a lot of research and found a lot of relevant questions and answers but couldn't quite figure out how to cater that information to my specific need.

I am working on a project to create a macro that will correct mistakes and fill in information commonly found in product catalogs that I work with.

One thing I am trying to accomplish is to give the value "unassigned" to each blank cell in a row that is marked "Y" in column B.

I've found out how to change every cell in those particular rows and have it adjust dynamically to the number of rows. What I can't figure out is how to do the same for the number of columns. In my code below everything between columns B and S is included. Column B will always be in the same spot but column S will not always be the last column.

Dim tracked As String
Dim endCell As Range
Dim endRow As Long
Dim endColumn As Long
Dim start As Long

endRow = ActiveSheet.Range("D2").End(xlDown).Row
endColumn = ActiveSheet.Range("A1").End(xlToRight).Column

Let tracked = "B2:" & "B" & endRow
Set trackItem = ActiveSheet.Range(tracked)
For Each y In trackItem
    If Left(y.Value, 1) = "Y" Then
        'start = y.Row
        'Set endCell = ActiveSheet.Cells(endColumn, start)
        ActiveSheet.Range("B" & y.Row & ":" & "S" & endColumn).Value = "Unassigned"
    End If
Next y

I included some code that I've left commented out so you can see what I've tried.

So, I can successfully change the value of all cells within that range but I need to know how to do it with a range where the number of columns will not always be the same. In addition, I want to select the blank cells only within this range and assign them a value. I imagine this will need to be done row by row as the correct criteria will not always be together.

if you last column is 12 and you are on row 6 then the assignment command in ActiveSheet.Range("B6:S12").Value = "Unassigned" ... that is probably not what you want — jsotola
anyway, y is a range already.. just do something like y.resize(1,endColumn).SpecialCells(xlCellTypeBlanks).Value = "Unassigned" you may have to use endColumn - 1 in the code because you are starting at column B — jsotola
Brilliant! I didn't even know about the resize function. This worked perfectly without the -1. — SupaSneak

ashleedawg ashleedawg · Accepted Answer · 2017-10-21T07:42:31

I'm surprised more people don't use 'UsedRange' when there is a need to loop through all the cells that have data on a sheet. (Just yesterday someone was complaining that it takes too long to loop through all 17,179,869,184 cells on a worksheet...)

This example lists & counts the "used" range, and will easily adapt to your needs.

Sub List_Used_Cells()
    Dim c As Range, x As Long
    For Each c In ActiveSheet.UsedRange.Cells
        Debug.Print c.Address & "  ";
        x = x + 1
    Next c
    Debug.Print
    Debug.Print " -> " & x & " Cells in Range '" & ActiveSheet.UsedRange.Address & "' are considered 'used'."
End Sub

VBA: Give a value to blank cells in rows that meet certain criteria

1 Answers