Hi Duane, is Text the default property for Range? I mean, could you have typed: If Len(rngTemp) > 0 ?
Giorgio
--- In MS_Access_Professionals@yahoogroups.com, "Duane" <duanehookom@...> wrote:
>
>
> I'm not sure how 15,000 cells will concatenate into a single but within
> Excel, you can create a function like:
>
> Function RangeConcatenate(rngIn As Range, _
> Optional strDelimiter As String = ", ")
> ' Usage
> ' A1 = Red
> ' A2 = White
> ' A3 = Blue
> ' RangeConcatenate(A1:A3) = Red, White, Blue
> ' RangeConcatenate(A1:A3," - ") = Red - White - Blue
>
> Dim rngTemp As Range 'each cell
> Dim strTemp As String
> Application.Volatile 'autoupdates
> For Each rngTemp In rngIn 'loop through each cell
> If Len(rngTemp.Text) > 0 Then 'ignore blank cells
> strTemp = strTemp & rngTemp & strDelimiter
> End If
> Next
> If Len(strDelimiter) > 0 Then 'remove the final delimiter
> strTemp = Left(strTemp, Len(strTemp) - Len(strDelimiter))
> End If
> RangeConcatenate = strTemp
> End Function
>
>
>
> Then in another cell, just enter something like:
>
> =RangeConcatenate(A1:A15000)
>
>
>
> Duane Hookom
>
> MS Access MVP
>
> --- In MS_Access_Professionals@yahoogroups.com, Barry White
> <imtigerwords@> wrote:
> >
> > Hello all,
> >
> > I have a column of Excel data that is all numbers, about 15,000 or so,
> one number in each cell at present.
> >
> > However, I want to turn that column into a row, where all the data can
> be seen from a single cell in that row.
> >
> > Example, I want to turn this:
> > 98164754
> > 15642829
> > 15636430
> > 15699463
> > 15600864
> > 15652140
> >
> > Into this, no spaces, but a comma between each number:
> >
> > 98164754,15642829,15636430,15699463,15600864,15652140
> >
> > Exactly how do I do this? Because, I know one simply fact, I am NOT
> doing this manually.
> >
> > Eric
> >
> >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
Tidak ada komentar:
Posting Komentar