VBA to fill many ranges of empty cells in columns A and B with different cell values

Armien7

New Member
Joined
Nov 18, 2021
Messages
13
Office Version
  1. 365
Platform
  1. Windows
I hope everyone is having a wonderful new year so far. I'm really struggling with a macro I'm trying to build and I hope I can get some advice here :)

I have a sheet (called: "Motors") with 24720 rows. I'm trying to fill empty ranges with different intervals in column A and B with the values of the last filled cells of each range in column A & B. So for example: Range (A16, B16 and C16 : A27, B27 and C27) is empty, and I want to have it filled with the cell values of A15, B15 and C15, etc. Since I have 20.000 rows+, it takes a lot of time to do it manually.

The big problem I have is that each empty range differs from the previous one (one may containt more empty rows than the other one). For clarification I have made the following screenshots:

Hopefully I can get some tips and new insights :)

Best regards,
Armien
 

Attachments

  • Filling empty ranges in Excell.png
    Filling empty ranges in Excell.png
    28.9 KB · Views: 25
  • Filling empty ranges 2.png
    Filling empty ranges 2.png
    37.5 KB · Views: 25
Very weird, same problem (the macro does run tho). It fills the first empty range, but stopt there, doen't copy Cells A28, B28 and C28 to the next empty range (which in this case is to ABC:123), but of course the interval of the next empty range where ABC:124 should be pasted in differs.
 
Upvote 0

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
If it's not copying down that suggests the cells aren't actually empty. You will need to find out what's in them.
 
Upvote 0
If it's not copying down that suggests the cells aren't actually empty. You will need to find out what's in them.
O wow you are totally right, I haven't thought of that for one second! I tried the macro in a new test sheet and it works perfectly! Thank you so much for your help Fluff, this truly made my day! :) I only need to find a way to "Delete" all "empty ranges" in a efficient way. Do you have a last small tip for this?
 
Upvote 0
O wow you are totally right, I haven't thought of that for one second! I tried the macro in a new test sheet and it works perfectly! Thank you so much for your help Fluff, this truly made my day! :) I only need to find a way to "Delete" all "empty ranges" in a efficient way. Do you have a last small tip for this?
Nvm filter and delete lol. It works totally fine! Again, thank you so much :)
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top