Fill Empty Blank Cells with value within a region horizontaly defined

Question

I'm trying to fill blank cells in a certain region with 0. The reagion should be defined in the current workbook but in sheet2 (not the current sheet). Also the place where it is supposed to fill is between columns BU:CQ in the current region (not all 100 000 000 lines). Just the number of lines that define the table between columns BU and CQ. I know the problem lies in defining the region... See the code below. What is missing?

Sub FillEmptyBlankCellWithValue()
    Dim cell As Range
    Dim InputValue As String

    On Error Resume Next
    InputValue = "0"
    For Each cell In ThisWorkbook.Sheets("Sheet2").Range(BU).CurrentRegion
      '.Cells(Rows.Count, 2).End(xlUp).Row
      If IsEmpty(cell) Then
        cell.Value = InputValue
      End If
    Next
 End Sub

I've this code that i'm positive that works! But i don't wnat selection! I want somthing that specifies the sheet and a fixed range. Now my idea is to replace "selection" with the desired range. - In this case in particular the range should be 1 - between BU:CQ; 2 - starting at row 2; 3 - working the way down until last row (not empty = end of the table that goes from column A to DE)

 Sub FillEmptyBlankCellWithValue()
     Dim cell As Range
     Dim InputValue As String
     On Error Resume Next
     For Each cell In Selection
        If IsEmpty(cell) Then
           cell.Value = "0"
        End If
     Next
 End Sub'

PS: And I also need to specify the sheet, since the button that will execute the code will be in the same workbook but not in the same sheet.

You should really use `Option Explicit` and I doubt that `ThisWorkbook.Sheets("Sheet2").Range(BU).CurrentRegion ` does even work. — iDevlop, Aug 30 '18 at 12:39
No! That one doesn't work. The only code that work is the second one that i added to my question. — jps17183, Aug 30 '18 at 13:24

iDevlop · Accepted Answer · 2018-08-30T12:58:42.833

1

Use SpecialsCells:

On Error Resume Next  'for the case the range would be all filled
With ws
    Intersect(.UsedRange, .Range("BU:CQ")).SpecialCells(xlCellTypeBlanks).Value = 0
End With
On Error GoTo 0

MUCH faster than looping !

edited Aug 30 '18 at 12:58

answered Aug 30 '18 at 12:23

iDevlop

24,841
11
90
149

1

@PatrickHonorez I would consider changing the `.UsedRange` to `.Range("A" & .Rows.Count).End(xlUp).Row` since @jps17183 specified the A column is non-sparse and `.UsedRange` isn't as reliable to use when the data is non-sparse. – jcrizk Aug 30 '18 at 14:53
@PatrickHonorez https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba/11169920#11169920 – jcrizk Aug 30 '18 at 14:54
1

@jcrizk you're perfectly right. It's just I am a bit lazy and sometimes I leave it to the OP to make a synthesis and pick the best parts of the proposed answers ;-) My goal with usedRange here was just to avoid filling the whole column. – iDevlop Aug 30 '18 at 14:59

Cyril · Answer 2 · 2018-08-30T13:30:40.047

0

Try using cells() references, such as:

For i = cells(1,"BU").Column to cells(1,"CQ").Column
    cells(1,i).value = "Moo"
Next i

In your current code you list Range(BU) which is not appropriate syntax. Note that Range() can be used for named ranges, e.g., Range("TheseCells"), but the actual cell references are written as Range("A1"), etc. For Cell(), you would use Cells(row,col).

Edit1

With if statement, with second loop:

Dim i as long, j as long, lr as long
lr = cells(rows.count,1).end(xlup).row
For i = 2 to lr 'assumes headers in row 1
    For j = cells(1,"BU").Column to cells(1,"CQ").Column
        If cells(i,j).value = "" then cells(i,j).value = "Moo"
    Next j
Next i

edited Aug 30 '18 at 13:30

answered Aug 29 '18 at 15:10

Cyril

6,448
1
18
31

Thanks, It works. The problem is that 1 - it replaces values, and i want it to skip cells with values; 2 - It doesn't continue to next row. – jps17183 Aug 30 '18 at 11:10
@jps17183 you can put in an if statement, which would take care of that issue. Will edit – Cyril Aug 30 '18 at 13:27
@jps17183 note that the loop is for row i, col j, and loops FIRST through the columns of a single row, then moves to the next row, hence the j loop is inside the i loop. – Cyril Aug 30 '18 at 13:31

jcrizk · Answer 3 · 2018-08-30T14:43:58.000

0

First off, you should reference the worksheet you're working with using:

Set ws = Excel.Application.ThisWorkbook.Worksheets(MyWorksheetName)

Otherwise VBA is going to choose the worksheet for you, and it may or may not be the worksheet you want to work with.

And then use it to specify ranges on specific worksheets such as ws.Range or ws.Cells. This is a much better method for specifying which worksheet you're working on.

Now for your question:

I would reference the range using the following syntax:

Dim MyRange As Range
Set MyRange = ws.Range("BU:CQ")

I would iterate through the range like so:

Edit: I tested this and it works. Obviously you will want to change the range and worksheet reference; I assume you're competent enough to do this yourself. I didn't make a variable for my worksheet because another way to reference a worksheet is to use the worksheet's (Name) property in the property window, which you can set to whatever you want; this is a free, global variable.

Where I defined testWS in the properties window:

Public Sub test()
    Dim MyRange As Range
    Dim tblHeight As Long
    Dim tblLength As Long
    Dim offsetLen As Long
    Dim i As Long
    Dim j As Long

    With testWS
        'set this this to your "BU:CQ" range
        Set MyRange = .Range("P:W")
        'set this to "A:BU" to get the offset from A to BU
        offsetLen = .Range("A:P").Columns.Count - 1
        'set this to your "A" range
        tblHeight = .Range("P" & .Rows.Count).End(xlUp).Row
        tblLength = MyRange.Columns.Count
    End With

    'iterate through the number of rows
    For i = 1 To tblHeight
        'iterate through the number of columns
        For j = 1 To tblLength
            If IsEmpty(testWS.Cells(i, offsetLen + j).Value) Then
                testWS.Cells(i, offsetLen + j).Value = 0
            End If
        Next
    Next

End Sub

Before:

After (I stopped it early, so it didn't go through all the rows in the file):

If there's a better way to do this, then let me know.

edited Aug 30 '18 at 14:43

answered Aug 29 '18 at 17:17

jcrizk

605
5
15

@jps17183 AFAIK, comments dont support writing actual code. – jcrizk Aug 30 '18 at 10:32
It doesn't even do nothing (not even an error). Its inocuous. But thanks anyway. – jps17183 Aug 30 '18 at 11:11
@jps17183 Well, that definitely is an English error (should be 'doesn't do *anything*'), but I digress. I changed my answer to something that works. – jcrizk Aug 30 '18 at 12:13
@jps17183 If the height is dependent upon column A's height, then that's easily done. Just give me a minute. – jcrizk Aug 30 '18 at 13:40
Thanks for the reply but what do you mean by "file max height"? Is it the max height of the table with values or the last row of the sheet? The row limit varies according to the length of column A with non-empty rows (consecutive values)... Collumn A always starts in the 2nd row and continues through an n-number of rows down (variable). Do you have any idea by the way? How can i make the routine last as long as the number of rows "n" defined by collumns A? – jps17183 Aug 30 '18 at 13:54
I know it is supposed to work but I got an error "object required". It stopped at --> Set MyRange = testWS.Range("BU:CQ") . Equivalent to your Set MyRange = testWS.Range("P:W") PS: Sorry for my English – jps17183 Aug 30 '18 at 13:58
@jps17183 Don't worry about the English; I thought you were a native English speaker being a smartass lol. `testWS` is the global worksheet variable on my worksheet. Just set your worksheet using `Set ws = Excel.Application.ThisWorkbook.Worksheets(YourWorksheetName)` and then you can set the range using `Set MyRange = ws.Range("P:W")`. Again, you'll need to change the range to the one *you* actually need instead of P:Q. – jcrizk Aug 30 '18 at 14:08
Forget it! It was the word "test" it's not doing anything before the word "WS". However it works, it doesn't function within the range "BU:CQ" (even after i adapt your interval to mine "P:W" ---> "BU:CQ"). Mysteriously, it rather works within the interval "A:AS" Do you know why? – jps17183 Aug 30 '18 at 14:29
Did you change `offsetLen = .Range("A:P").Columns.Count - 1` to `offsetLen = .Range("A:BU").Columns.Count - 1`? – jcrizk Aug 30 '18 at 14:45
No I didn't! Now i realized i didn't understood the syntax. IT WORKED!! But the "prize will go to Patrick Honorez because his code is much faster and more efficient. But you rock man... Thanks – jps17183 Aug 30 '18 at 14:56

Fill Empty Blank Cells with value within a region horizontaly defined

3 Answers3