Hi,

How can I fill below cells which are blank (Empty Cells) with the above cell data? Like in the following example:

Column: A
Cell 1: 54001
Cell 2:
Cell 3: 54003
Cell 4:
Cell 5:
Cell 6:
Cell 7: 56321
Cell 8:
Cell 9:
-------------------------The reuslt should be like the following in RED:
Cell 1: 54001
Cell 2: 54001
Cell 3: 54003
Cell 4: 54003
Cell 5: 54003
Cell 6: 54003
Cell 7: 56321
Cell 8: 56321
Cell 9: 56321


Please help me with function/code. Because I need to modify hundreds of records on several sheets. It is very hard to do it manually.


Thanks.

Hi
Paste this code into a module in your workbook. It will deal with 1000 rows in Column A of your worksheet.
Let me know if you need to cover more columns.

Sub CellCheckForBlank()
'
' Check for Blank Cells and populate with value above
'
For Count = 1 To 1000
myVal = Range("A" & Count)
Range("A" & Count + 1).Select
If Range("A" & Count + 1) = "" Then
Range("A" & Count + 1) = myVal
End If
Next Count
End Sub


Tony (OTPM)

Thanks for your reply. I tried this code but it is not working. I pasted this code in Excel work book in Visual Basic Module.

Please help me to figure it out.

Regards.

Hi
I tested it fully and it worked fine. Can you post a sample of your spreadsheet and I will attempt to provide a fix.
Kind regards
Tony(OTPM)

Thanks again. The following is the Test Excel work book attached for review.

Attached Files

Test.xlsm (12.0 KB, 11 views)

Hi
I tested the macro it it seemed to work fine for me. If I understand you correctly it should do the following:
1. As Cell A6 is blank it should copy the contents of Cell A5 into Cell A6.
2. As Cell A11 and A12 are blank then copy the contents of Cell A10 into Cells A11 and A12.
and so on..................
The macro will run for 1000 lines. So Cell A30 will be copied to Cells A31 through A1000.

If this is not what you want then please explain what you expect to happen and I will see what I can do.
Tony(OTPM)

Yes. You are right. That is what I want. But when I tried it to copy the blank cells by dragging A5 Cell, it copies as follows:

CopyDataToCell-5
CopyDataToCell-6
CopyDataToCell-10
CopyDataToCell-11
CopyDataToCell-12

Actually it should copy:

CopyDataToCell-5
CopyDataToCell-5
CopyDataToCell-10
CopyDataToCell-10
CopyDataToCell-10

Where I am doing wrong in executing the function. Please help.

Thanks.

Hi
Now I understand why it did not run for you. You need to select the macro and choose run. It will then automatically fill the blank cells down to row 1000.
Hope this helps.
Tony(OTPM)

Wonderful! Thank you very much for your kind support.

Regards.