How to concatenate if another cell blank

Posted by sam on November 14, 2000 6:26 PM

I import a text file which has textwrap on col T (can be several lines),at the moment I copy/paste to fix up: I'm sure there must be a better way.... T needs to be concatenated every time A (or any of A to S!) is blank, and then the row with the surplus text deleted.
Thanks if you can help !

Posted by marbel on November 15, 2000 8:08 AM

some combination of IF / ISBLANK / CONCATENATE in a new column?

eg: =IF(ISBLANK(A1)=TRUE,CONCATENATE(your cells here),your cells here)

If you clarify the problem a little more I might be able to be more specific on the solution. Hope you get what you're lookin' for...
mb

Posted by sam on November 15, 2000 11:14 AM

Posted by sam on November 15, 2000 12:11 PM

Oops! To clarify...........

Sorry , marbel !
I was wondering what the vb code would be to concatenate (x,15) with (x-1,15) if (x,1) is blank, and then delete the entire row that is no longer required.

Because I can't do this, I have a macro pasting the whole report into new workbooks until I have corrected the imported file's textwrap and then sort the file the way it is required. This works ok but seems a bit daft.......

Thanks and Sorry again !.........Sam

Posted by Tim Francis-Wright on November 15, 2000 3:04 PM

Re: Oops! To clarify...........

Here is something that might help...
it's based on one of Celia's myriad answers
on this board (from 18 October 2000):

Sub trythis()
Dim TheRange As Range, LastRow As Long, FirstRow As Long, i As Long
Const DataCol = 20
Const TestCol = 1

Set TheRange = ActiveSheet.UsedRange
LastRow = TheRange.Cells(TheRange.Cells.Count).Row
FirstRow = TheRange.Cells(1).Row + 1
For x = LastRow To FirstRow Step -1
If Cells(x, TestCol) = "" Then
Cells(x - 1, DataCol) = Cells(x - 1, DataCol) & Cells(x, DataCol)
Rows(x).Delete
End If
Next x
End Sub

(The macro goes backwards from the end to
the beginning so it deletes rows only after
it has evaluated them...)

HTH!