I have a data set that is 48 columns wide by 90 rows deep. I need to put all
of the rows into one long row to graph it. I can easily do a macro to just
cut and paste but it need to paste at the first empty cell of the top row.
What is the easiest way to find this cell? I can not find anything in GoTO
which helps.

Thanks in advance
--
Frustrated user

unless you are using 2007 you will probably have problems in that you can't
have a row with more that 256 entries for earlier versions.

A simple way to do what you want is to first transpose all of the data
Use a macro to just paste into one long column

use filter to remove all of the blank cells

use transpose again to get into a row


"Phrontis" wrote:

I have a data set that is 48 columns wide by 90 rows deep. I need to put all
of the rows into one long row to graph it. I can easily do a macro to just
cut and paste but it need to paste at the first empty cell of the top row.
What is the easiest way to find this cell? I can not find anything in GoTO
which helps.

Thanks in advance
--
Frustrated user

I'm guessing that you meant to write that you want everything in one column.

Column B is copied to the bottom of column A, then column C at the bottom of
column A, ...

If that's the case, you could use:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim iCol As Long
Dim LastCol As Long
Dim FirstCol As Long
Dim RngToCopy As Range

Set wks = Worksheets("sheet1")

With wks
FirstCol = 2
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

For iCol = FirstCol To LastCol
Set RngToCopy = .Range(.Cells(1, iCol), _
.Cells(.Rows.Count, iCol).End(xlUp))
RngToCopy.Copy _
Destination:=.Cells(.Rows.Count, "a").End(xlUp).Offset(1, 0)
Next iCol

'clean up old data
.Range(.Cells(1, FirstCol), .Cells(1, LastCol)) _
.EntireColumn.ClearContents
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Phrontis wrote:

I have a data set that is 48 columns wide by 90 rows deep. I need to put all
of the rows into one long row to graph it. I can easily do a macro to just
cut and paste but it need to paste at the first empty cell of the top row.
What is the easiest way to find this cell? I can not find anything in GoTO
which helps.

Thanks in advance
--
Frustrated user

--

Dave Peterson

to answer your question one method in VBA would be to

add to your macro
cells("IV1").select
Selection.End(xlToLeft).Select
cl = activecell.column+1

cl will be the column with no data in it or to the right in row 1

"bj" wrote:

unless you are using 2007 you will probably have problems in that you can't
have a row with more that 256 entries for earlier versions.

A simple way to do what you want is to first transpose all of the data
Use a macro to just paste into one long column

use filter to remove all of the blank cells

use transpose again to get into a row


"Phrontis" wrote:

I have a data set that is 48 columns wide by 90 rows deep. I need to put all
of the rows into one long row to graph it. I can easily do a macro to just
cut and paste but it need to paste at the first empty cell of the top row.
What is the easiest way to find this cell? I can not find anything in GoTO
which helps.

Thanks in advance
--
Frustrated user