# Count blank cells, copy to every sixth row

#### tape deck

##### Board Regular
Book1
ABCD
1bobmm1
2andymm1
3almm1
4stewartag1
5smithag1
6terrydc1
7jasondc1
8jayas1
9albertyn1
10
Sheet1

I have a very simple spreadsheet such as the one above.

Sheet two of the workbook is a simple column that is 456 cells long. Of this column, there are six data sets, every sixth cell, basically. So data set one is: A1, A7, A13, etc. Data set two is: A2, A8, A14, etc. And so on.

What I'd like to do is to have a macro count the blank cells in each of the six data sets, and based on which data set has the most blank cells, copy the names from the other spreadsheet (column A) into that data set, starting from the top. This is based on the contents of column B; when all of the names that are "MM1" have been copied, the macro would than calculate the number of blank cells per data set again...and then copy over the "AG1's."

Mind you, the actual spreadsheet of names is 400+ lines long. Is this possible?

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### tape deck

##### Board Regular
Alternately, if counting every sixth row is problematic, I could set up the second spreadsheet as such...
Book1
ABCD
1DataSetLine#Name
2a1
3a2
4a3
5b4
6b5
7b6
8c7
9c8
10c9
11d10
12d11
13d12
14e13
15e14
16e15
17f16
18f17
19f18
Sheet1

Then the the formula could count the cells of a's, b's, c's, d's, e's and f's where the offset two columns to the right was blank. Having the b column would enable me to resort it back into the original row order.

#### tape deck

##### Board Regular
Let's try this one last time. I'm slowly thinking my way through the dilemma. We can probably forget the previous posts. Here is Sheet 1:
Book1
ABCD
1NAMEJOB
2bobMM1
3alMM1
4jimMM1
5terryBM1
6andyBM1
7ralphHM1
8kevinHM1
9taraFC1
10moniqueFC1
11jimboLT1
12willieLT1
13francisPS1
15wolfmanABF1
16chuchyABE1
17satanAS1
18alfQM1
19raulRP1
Sheet1

Replies
7
Views
241
Replies
18
Views
224
Replies
1
Views
101
Replies
0
Views
124
Replies
9
Views
78

1,191,614
Messages
5,987,690
Members
440,104
Latest member
thigarette

### 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.

### Which adblocker are you using?

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

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