Modifying my code

[smilelover]

I have successfully done what I need to do, this is my modified VBA code:

Private Sub CommandButton1_Click()
colarow = 1
For colbrow = 1 To 7000 Step 7
Range("=Sheet4!A" & colbrow).Formula = ("=Sheet1!A" & colarow)
colarow = colarow + 1
Next
End Sub

because on Sheet1!A I have a list of stuff and they may increase or decrease any thing, in this point I assume that they won't go over 1000.
I wanted to create a list on Sheet4!A which are identical to Sheet1!A except I have 7 blank rows in between each data and I've achieved that.

Problem 1: How can I hide or delete the 0 entries? For example I have 10 data on Sheet1!A, that means that I would have 70 rows on Sheet4!A which are significant for me, the other 6930 rows of 0's and empties are useless which I hope to get rid, ideas?

Problem 2: I wish the list on Sheet4!A to start every time when I open up the spreadsheet, without the need to click the Command Button, I know it will sync the two rows after I click it once, but I hope to reduce it to automatic, without the need to click the Command Button every time I start up.

You guys are wonderful, keep up the good work!!!

 

[moonfish]

If you rename your sub to Workbook_Open() it will be executed upon startup.

You can use this to hide the 6 rows below your current selection:
Range("=Sheet4!A" & colbrow).Offset(1, 0).Rows("1:6").EntireRow.Hidden = True

Result:

Private Sub Workbook_Open()
colarow = 1
For colbrow = 1 To 7000 Step 7
Range("=Sheet4!A" & colbrow).Formula = ("=Sheet1!A" & colarow)
Range("=Sheet4!A" & colbrow).Offset(1, 0).Rows("1:6").EntireRow.Hidden = True
colarow = colarow + 1
Next
End Sub

 

[smilelover]

Thanks moonfish!!
Unfortunately, this is not quite the thing I wanted.
You're hiding all the blank rows, including those which are between significant data.

For example, my code will generate something like this, for 5 data

a
(blank)
(blank)
(blank)
(blank)
(blank)
(blank)
(blank)
b
(blank)
(blank)
(blank)
(blank)
(blank)
(blank)
(blank)
c
(blank)
(blank)
(blank)
(blank)
(blank)
(blank)
(blank)
0
(blank)
(blank)
(blank)
(blank)
(blank)
(blank)
(blank)
0
(blank)
(blank)
(blank)
(blank)
(blank)
(blank)
(blank)

etc...

I wanted to hide from the first 0 and onwards since I have only 3 significant data, and the number of data will change anytime, so I need it to sync with the original row

a
b
c

Sorry for causing trouble.

Oh yeah, I don't know why but the Workbook_Open() doesn't seems to be working.

 

[smilelover]

Anyone please help me

 

[RichardMGreen]

The workbook_open() routine needs to go in the Thisworkbook module, not in a standard module.

Can you post some data of what your original list is like and what you want to see at the end of it?

 

[smilelover]

I plan to ask part by part, but I wanted to do this:

Sheet 1:
a 1 2 3
b 11 12 13
c 21 0 23
d 31 32 33
e 41 42 43
f 51 52 53
g 61 62 63
h 71 72 73
i 81 82 83
j 91 92 93
k 101 102 103
l 111 112 113
m 121 122 123

Sheet 2:

1
2
3
4
5
6
7
8

Sheet 4: Now I want it to look like:
a 1 1 2 3
2 1 2 3
3 1 2 3
4 1 2 3
5 1 2 3
6 1 2 3
7 1 2 3
8 1 2 3
b 1 11 12 13
2 11 12 13
3 11 12 13
4 11 12 13
5 11 12 13
6 11 12 13
7 11 12 13
8 11 12 13

until m
and hope that it sheet 4 syncs with sheet1 and sheet2 when I made any changes on the 2 sheets
So far I have done the first column of sheet 4, thats all.
I know it looks messy

 

[smilelover]

oops it was not on format, give me a sec

 

[RichardMGreen]

Sorry, but I can't make sense of that.
Can you post a screenshot of before and after using one of the board guides?

 

[smilelover]

Screenshots are uploaded online

http://www.sendspace.com/file/sttk3o

Can't figure out how to use the Colo's HTML Maker

Currently only available to work on the first column on Sheet 3,
Sorry for making a fuss on you guys

 

[RichardMGreen]

Those images haven't come through.
You either need to upload them somewhere like PhotoBucket or use the HTML maker (see the 4th sticky on this part of the board).