How to Find the Next non-empty cell using vba excel (plus merge it)

[killpaddy]

Hi,

I have a afew columns of data related to eachother. Column A contains text in some cells then afew blank cells then another cell with text etc.
I would like to merge the text cells with all the blank cells below it (until the next non-empty cell is reached).
However, I dont know the code to 'find next non-empty cell'
Here's my code so far:

Dim startfrom As Long
Dim Merge1 As Long
Dim Merge2 As Long

startfrom = 1
Do
    Merge1 = Range("A" & startfrom & ":A" & LastRow).Find("").Row - 1
    Merge2 = Range("A" & Merge1 & ":A" & LastRow).Find([!""]).Row  'error here
    Range("A" & Merge1 & ":A" & Merge2 - 1).Merge
    startfrom = Merge2

Loop Until Range("A" & LastRow) Is Not Nothing 'error probably here aswell

Ive tried different combinations of the '[!""]' and none of them work. can anyone suggest code that would work?

Also how to you get it to 'merge and centre'?

And does anyone know if I save this workbook as a .CSV file whether it will still contain the merged cells when opened somewhere else?

Any help would be appreciated, Thanks

 

[patel45]

attach please a link to a sample of file

 

[killpaddy]

https://docs.google.com/spreadsheet/ccc?key=0As_H1bFKIn8FdFRRME1VYXNfLUV2TkJEdEZDejRTbFE

 

[killpaddy]

E.g:

'Quick Turn 8; 10
8 Station; 8 Position'

will be merged over cells A2 to A6

 

[jim may]

Sample Table BEFORE Running of Macro (Foo)...

Excel 2010
	A	B	C	D
1	COL_A_HEADER	B	C	D
2	AAA	121	359	236
3		395	175	255
4		198	449	255
5		238	133	151
6		254	208	109
7	BBB	437	464	349
8		477	436	358
9		223	290	383
10		178	124	148
11		270	335	128
12	CCC	492	249	428
13		170	366	131
14		294	470	420
15		411	204	261
16		368	107	293
17	DDD	208	300	353
18		129	445	289
19		354	185	292
20		365	430	455
Sheet1

Paste below into a standard Module:

Sub Foo()
Dim arr()
Dim LR As Long, ct As Long, t As Long
Dim Rng As Range, c As Range
Application.ScreenUpdating = False
LR = Range("B" & Rows.Count).End(xlUp).Row
ct = 1
Set Rng = Range("A2:A" & LR)
For Each c In Rng
    If c.Value <> "" Then
        ReDim Preserve arr(ct)
            arr(ct) = c.Row
        ct = ct + 1
    End If
Next c
For t = 1 To UBound(arr) - 1
With Range(Cells(arr(t), "A"), Cells(arr(t + 1) - 1, "A"))
    .Merge
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
End With
Next t
With Range(Cells(arr(t), "A"), Cells(LR, "A"))
    .Merge
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
End With
Application.ScreenUpdating = True
End Sub

Back up your Data before trying this...

 

[killpaddy]

Thanks jim,

That code worked perfectly for what I needed, and my code updates the sheet with new data before this bit of code anyway.

I just have a couple of general interest questions about your code (as im a bit of a novice in VBA)

Does the 'ScreenUpdating' = Ture/False, speed up the program? I wasnt aware of it and I have a coulpe of other bits of code with loops in that I could use it for, are there any conditions i doesnt work under.
What is 'arr()' is it to do with arrays? I havn't used them in VBA yet.

Also, do you know if the cells will remain merged if I save as a .csv file?

 

[jim may]

Does the 'ScreenUpdating' = True/False, speed up the program? I wasnt aware of it and I have a coulpe of other bits of code with loops in that I could use it for, are there any conditions i doesnt work under.

It eliminates any SCREEN-FLASHING/MOVEMENT WHILE MACRO CHANGES THE SHEET -- it does also speed up code (I'm pretty sure).. It falls under BEST PRACTICES (to me)..

What is 'arr()' is it to do with arrays? I havn't used them in VBA yet.

Yes arr() is an array, a one-dimensional type (used here to capture the row numbers of your Column A Text cells)

Also, do you know if the cells will remain merged if I save as a .csv file?

Not sure,,,,, Try it and Find out

Glad it helped Jim

 

[patel45]

-

 

[killpaddy]

Ok thanks for the tips.

It does work when saving as a .csv. However I now have a new problem.

I'm saving as a .csv so I can upload it to an online stocklist (which has a system for 'bulk importing' data in a csv format), unfortunately the merged cells don't trasfer to this (I dont think it's possible, I have an alternative anyway). The only problem is that my data in column A (as can be seen in the google doc) the text has line separators in:
e.g.
Quick Turn 8; 10
8 Station; 8 Position

in csv text file appears as:
"Quick Turn 8; 10
8 Station; 8 Position",Turning Holder,C3-*CE-QT10-030026M,0,,

The website reads this as two separate cells with text in, I want it to read the same as in excel (like the google doc).

Im thinking perhaps something like '...8; 10[tab] 8 Station...' and I want to be able to do this using the VBA code rather than manually

Have any Ideas?

 

[killpaddy]

Ive outlined the problem a bit clearer on this new thread:

http://www.mrexcel.com/forum/excel-...ce-visual-basic-applications.html#post3241856