• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need code that can detect empty rows and select the two neighbouring cells with data in

VBXL

Member
Hello there, I am working through thousands of rows of data.

I want to use Fill>Series>Trend to fill the gaps. At the moment, I highlight from the next row with data in it, the empty rows and then the previous row with data in it. The number of gaps between varies.

Is there a way to get Excel to do this automatically? Perhaps I select the row with data before the blanks, and then get Excel to select the range until it finds the next row with data in it?

So it would select like this:



Thanks for your help.
 

VBXL

Member
Thanks for your reply.

Sheet2 demonstrates what I need.


I'm not looking to automate the entire process, because I need to ensure the data is valid at all stages. So, I am binding keys to macros. Ctrl & SPACE to insert a new row, CTRL & D to fill>series>trend. CTRL & D works well, but I am having to manually highlight cells before initiating. It would be much better to place the focus in the cell with data above the gaps and then have Excel check the data in that cell with the next populated one to figure out the trend and then keep filling in the blanks.

 

VBXL

Member
I don't quite understand. Isn't and do what?

I wrote that, I have elaborated and provided sample data as requested and that's why the image is different as is the sample workbook.
 

Attachments

Marc L

Excel Ninja
Hello, according to the previous post attachment a VBA demonstration (v2) as a beginner starter :​
Code:
Sub Demo1()
        Dim Ra As Range
   With Sheet2.[A1].CurrentRegion.Columns
        If Application.CountBlank(.Item(1)) Then For Each Ra In .Item(1).SpecialCells(4).Areas: Ra = Ra(0): Next
        If Application.CountBlank(.Item(2)) Then
            For Each Ra In .Item(2).SpecialCells(4).Areas
                Ra = .Parent.Evaluate(Ra(0).Address & "+ROW(1:" & Ra.Count & ")")
            Next
        End If
    End With
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 

VBXL

Member
Hello Marc, thanks for your reply. That's impressive, although it's actually too powerful!

Instead of filling in all blanks within the sheet, I want to fill in only the blanks below the selected cell/row. Also, it needs to check for gaps in column A & B rather than only column B.
 

VBXL

Member
I don't understand, when I test it, it autofills column B only. I'm saying it should deal with both column A and B. As I said, it's also automatically filling every blank in the whole sheet, not just those below the selected cell.

I've updated the workbook so that you can see it only targets one selection at a time. Now there is a sheet called "Example" to show how it deals with different scenarios. When the first column is missing a number, it should fill it in as well as the value for the second column. When there is data in the first column but the second column is empty, it needs to consider the value of column A in order to populate the correct value in column B (row 35 for example).
 

Attachments

Last edited:

VBXL

Member
Here's an example:

I've selected A1. I want to autofill series>trend in columns A & B.
A2:A3 should be filled with 165, B2 should be filled with 165443 and B3 with 165444. All other blank rows should be left empty.

Process should be select cell in row above blank cells in columns A and B. Execute code, which autofills in series>trend until the next row contains data. Then the code stops.

When running, it needs to be aware of a change from one number to the next in column A. For example, cell A33 contains 165. The next row contains 166. That means, B34 needs to be 166001.
 

VBXL

Member
Thanks for your reply.

That's quite good. Two issues though.

1. It still checks and changes all empty cells on the worksheet rather than those only immediately after the highlighted cell.
2. It doesn't check and therefore it produces errors like in B34, it should recognise the value in A34 is 166 and therefore looking at B35, realise the number should be 166001 in B34.
 

Marc L

Excel Ninja
According to your post #3 attachment I forgot to update the first column​
so try with the post #4 new updated version with the same post #3 attachment.​
But can't work with the new rules introduced in your post #8 attachment ...​
 

Marc L

Excel Ninja
1. It still checks and changes all empty cells on the worksheet rather than those only immediately after the highlighted cell.
If you just want to update the selection then replace the source range in code with Selection statement.​
 

VBXL

Member
That's what I want. Basically, I want to avoid having to highlight cells and have the code work locally rather than the whole sheet.
 

VBXL

Member
I'm not sure how I can elaborate any more than I did before.

"Process should be select cell in row above blank cells in columns A and B. Execute code, which autofills in series>trend until the next row contains data. Then the code stops."

So, if A1 or B1 are the selected cell, A2:B3 should be autofilled as they are in the next blank rows afterwards and then the code should stop.
 

Attachments

Last edited:

Marc L

Excel Ninja
A very beginner starter VBA demonstration :​
Code:
Sub Demo2()
    Dim R&, F%
        R = ActiveCell.Row
    Do
        F = 1
        R = R + 1
        If IsEmpty(Cells(R, 1)) Then F = 0: Cells(R, 1) = Cells(R - 1, 1)
        If IsEmpty(Cells(R, 2)) Then F = 0: Cells(R, 2) = Cells(R - 1, 2) + 1
    Loop Until F
End Sub
 

VBXL

Member
Now I totally love you! Thank you so much.

Could you please explain how this works? I'm really keen to understand.

I understand that Dim defines the name of something (essentially like giving something a nickname to make coding shorter).
 

Marc L

Excel Ninja
No, as explained in VBA help - open it, all is there, a must read ! - Dim just qualify the variable data type,​
optional as can work without as Variant ...​
 

VBXL

Member
I understand how to use it, I want to understand how the code works. Like, can you add comments to explain how each line works?
 

VBXL

Member
I tried it on another sheet with five empty rows and it brought back an error

Code:
If IsEmpty(Cells(R, 2)) Then F = 0: Cells(R, 2) = Cells(R - 1, 2) + 1
Run-time error '13'
Type mismatch

: Cells(R, 2) = Cells(R - 1, 2) + 1

Why would this happen?
 

Marc L

Excel Ninja
Maybe you try on a text instead of a number ?​
As made with post #3 attachment, if you can reproduce the same issue with it then revert each step how to raise this error ...​
 

VBXL

Member
That's probably the reason, it was text instead. Could it test whether it's text or numbers and if text, bring up MsgBox "This selection is text, this keyboard shortcut will only work with numbers"?
 

Marc L

Excel Ninja
As it does not care of the selection but you can test each cell via IsNumeric VBA function ...​
 
Top