Hi,

I have data in column O (starting from row number 2) and it could extend up
to let's say till column T within a worksheet called "basesheet". the number
of rows will be variable.

There is a pattern for data being filled up in these columns. Column O is
never empty. But, ...if column P is empty then all columns after that will
be empty for that row. Similarly if column Q is empty then all columns after
that will be empty and so on...

I want to copy all the Non-empty cells from column O to column T in to
another New Workbook, starting from Cell A1 within a SINGLE COLUMN. (using
Transpose)

Whats the most efficient way (time- wise). I have pasted my attempt below.

Please tell the places where I can make the code faster. ( Basically, I have
to do this for around 70 worksheets and many rows, thats why Iam asking for
a more efficient code).



Option Explicit

Sub try()

Dim NewWorkBookName As String
Dim i As Long

Workbooks.Add
NewWorkBookName = ActiveWorkbook.Name

ThisWorkbook.Worksheets("basesheet").Activate

For i = 2 To Range("o65536").End(xlUp).Row

Cells(i, Range("iv" & i).End(xlToLeft).Column).Select
Debug.Assert (ActiveCell.Row < 9)
If ActiveCell.Column = 15 Then
ActiveCell.Copy
Else
Range(ActiveCell, Cells(i, "o")).Copy
End If

Workbooks(NewWorkBookName).sheets("sheet1").Activa te
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks
_
:=False, Transpose:=True
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select

ThisWorkbook.Worksheets("basesheet").Activate
Next i

End Sub


Thanks a lot,
Hari
India

Something like this will do it.
You will have to work it a bit further out to accomodate the multiple
workbooks.
It presumes that if a cell is empty it will have to move to the next column
in the sheet.


Sub test()

Dim arrInPut
Dim arrOutput(1 To 65536, 1 To 1)
Dim i As Long
Dim c As Long
Dim n As Long
Dim x As Long

arrInPut = Range(Cells(2, 15), Cells(2, 15).SpecialCells(xlLastCell))

For c = 1 To UBound(arrInPut, 2)
For i = 1 To UBound(arrInPut)
If arrInPut(i, c) = "" Then
'empty cell, so move to next column
Exit For
End If
If n = 65536 Then
'going to exceed sheet rows, so start new array and column
x = x + 1
n = 0
With Sheets(2)
.Range(.Cells(x), .Cells(65536, x)) = arrOutput
End With
End If
n = n + 1
arrOutput(n, 1) = arrInPut(i, c)
Next
Next

x = x + 1

With Sheets(2)
.Range(.Cells(x), .Cells(n, x)) = arrOutput
End With

End Sub


RBS


"Hari Prasadh" wrote in message
...
Hi,

I have data in column O (starting from row number 2) and it could extend
up to let's say till column T within a worksheet called "basesheet". the
number of rows will be variable.

There is a pattern for data being filled up in these columns. Column O is
never empty. But, ...if column P is empty then all columns after that will
be empty for that row. Similarly if column Q is empty then all columns
after that will be empty and so on...

I want to copy all the Non-empty cells from column O to column T in to
another New Workbook, starting from Cell A1 within a SINGLE COLUMN. (using
Transpose)

Whats the most efficient way (time- wise). I have pasted my attempt below.

Please tell the places where I can make the code faster. ( Basically, I
have to do this for around 70 worksheets and many rows, thats why Iam
asking for a more efficient code).



Option Explicit

Sub try()

Dim NewWorkBookName As String
Dim i As Long

Workbooks.Add
NewWorkBookName = ActiveWorkbook.Name

ThisWorkbook.Worksheets("basesheet").Activate

For i = 2 To Range("o65536").End(xlUp).Row

Cells(i, Range("iv" & i).End(xlToLeft).Column).Select
Debug.Assert (ActiveCell.Row < 9)
If ActiveCell.Column = 15 Then
ActiveCell.Copy
Else
Range(ActiveCell, Cells(i, "o")).Copy
End If

Workbooks(NewWorkBookName).sheets("sheet1").Activa te
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select

ThisWorkbook.Worksheets("basesheet").Activate
Next i

End Sub


Thanks a lot,
Hari
India

Hari

There is little need to activate or select. Each time you do Excel
has to re-draw the screen which takes considerable time.

If you are likely to switch back and forth between workbooks you can
assign them to object variables and use these to refer to them quickly
and accurately without selection or activation.

I suspect the code below, which while definately not the best will run
about 100 times quicker than loads of activating and selecting

Sub CopyNonBlankData()
Dim NewWb As Workbook, NewWks As Worksheet
Dim CurrWks As Worksheet, rng As Range
Dim lLastFixedRow As Long, iLastCol As Integer
Dim lLastVariableRow As Long

Set CurrWks = ThisWorkbook.Worksheets("basesheet")
Set NewWb = Workbooks.Add
Set NewWks = NewWb.Worksheets(1)
lLastFixedRow = CurrWks.Range("O65536").End(xlUp).Row

For Each rng In CurrWks.Range("O2:O" & lLastFixedRow)
iLastCol = rng.Offset(0, 6).End(xlToLeft).Column
lLastVariableRow = NewWks.Range("A65536").End(xlUp).Row + 1
rng.Resize(, iLastCol - 14).Copy
NewWks.Range("A" & lLastVariableRow).PasteSpecial _
Paste:=xlPasteValues, Transpose:=True
Next rng
End Sub


Nick Hodge
Microsoft MVP - Excel
Southampton, England

On Sun, 30 Jan 2005 19:57:19 +0530, "Hari Prasadh"
wrote:

Hi,

I have data in column O (starting from row number 2) and it could extend up
to let's say till column T within a worksheet called "basesheet". the number
of rows will be variable.

There is a pattern for data being filled up in these columns. Column O is
never empty. But, ...if column P is empty then all columns after that will
be empty for that row. Similarly if column Q is empty then all columns after
that will be empty and so on...

I want to copy all the Non-empty cells from column O to column T in to
another New Workbook, starting from Cell A1 within a SINGLE COLUMN. (using
Transpose)

Whats the most efficient way (time- wise). I have pasted my attempt below.

Please tell the places where I can make the code faster. ( Basically, I have
to do this for around 70 worksheets and many rows, thats why Iam asking for
a more efficient code).



Option Explicit

Sub try()

Dim NewWorkBookName As String
Dim i As Long

Workbooks.Add
NewWorkBookName = ActiveWorkbook.Name

ThisWorkbook.Worksheets("basesheet").Activate

For i = 2 To Range("o65536").End(xlUp).Row

Cells(i, Range("iv" & i).End(xlToLeft).Column).Select
Debug.Assert (ActiveCell.Row < 9)
If ActiveCell.Column = 15 Then
ActiveCell.Copy
Else
Range(ActiveCell, Cells(i, "o")).Copy
End If

Workbooks(NewWorkBookName).sheets("sheet1").Activa te
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks
_
:=False, Transpose:=True
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select

ThisWorkbook.Worksheets("basesheet").Activate
Next i

End Sub


Thanks a lot,
Hari
India

Hi RBS,

Thnx for your code.

Just a ..Presently your code is copying data only up to the column based on
row number 2's non empty column. So, if I have data till column Q for row
number 2 and if any of the rest of the rows I have data beyond column Q ,
then data till column Q only is considered. Also the macro is copying
entries column wise.. That is all the non-empty entries in column O, then P
and so on. In the present case I prefer a row wise copying macro, which Nick
has provided.

One syntax related doubts in your code. You have used -- With Sheets(2) --
in your code. I have never come across a numeral within sheets(), I have
seen it to be a string or a string surrounded by quotations. Is this 2 same
as the code name reference used by Excel (I first came across this code name
reference , yesterday, while browsing through one of J Peltier's article).
Is there some source where I may read a little bit about what this code name
is about.

Thanks a lot,
Hari
India

Hi Nick,

Thnx a ton for your help. Just what I needed. It pastes the data from one
row then to second and so on.

Im learning the * P's and Q's* of VBA through macro recording, hence the
clutter. Will try to get comfortable with using object variables by using F8
in your code. (that way I can make better sense)

Thanks a lot,
Hari
India

"Nick Hodge" wrote in message
...
Hari

There is little need to activate or select. Each time you do Excel
has to re-draw the screen which takes considerable time.

If you are likely to switch back and forth between workbooks you can
assign them to object variables and use these to refer to them quickly
and accurately without selection or activation.

I suspect the code below, which while definately not the best will run
about 100 times quicker than loads of activating and selecting

Sub CopyNonBlankData()
Dim NewWb As Workbook, NewWks As Worksheet
Dim CurrWks As Worksheet, rng As Range
Dim lLastFixedRow As Long, iLastCol As Integer
Dim lLastVariableRow As Long

Set CurrWks = ThisWorkbook.Worksheets("basesheet")
Set NewWb = Workbooks.Add
Set NewWks = NewWb.Worksheets(1)
lLastFixedRow = CurrWks.Range("O65536").End(xlUp).Row

For Each rng In CurrWks.Range("O2:O" & lLastFixedRow)
iLastCol = rng.Offset(0, 6).End(xlToLeft).Column
lLastVariableRow = NewWks.Range("A65536").End(xlUp).Row + 1
rng.Resize(, iLastCol - 14).Copy
NewWks.Range("A" & lLastVariableRow).PasteSpecial _
Paste:=xlPasteValues, Transpose:=True
Next rng
End Sub


Nick Hodge
Microsoft MVP - Excel
Southampton, England

On Sun, 30 Jan 2005 19:57:19 +0530, "Hari Prasadh"
wrote:

Hari

The Macro recorder is oft overlooked as a great way of learning code. I
still use it regularly for the less often used objects.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England

"Hari Prasadh" wrote in message
...
Hi Nick,

Thnx a ton for your help. Just what I needed. It pastes the data from one
row then to second and so on.

Im learning the * P's and Q's* of VBA through macro recording, hence the
clutter. Will try to get comfortable with using object variables by using
F8 in your code. (that way I can make better sense)

Thanks a lot,
Hari
India

"Nick Hodge" wrote in message
...
Hari

There is little need to activate or select. Each time you do Excel
has to re-draw the screen which takes considerable time.

If you are likely to switch back and forth between workbooks you can
assign them to object variables and use these to refer to them quickly
and accurately without selection or activation.

I suspect the code below, which while definately not the best will run
about 100 times quicker than loads of activating and selecting

Sub CopyNonBlankData()
Dim NewWb As Workbook, NewWks As Worksheet
Dim CurrWks As Worksheet, rng As Range
Dim lLastFixedRow As Long, iLastCol As Integer
Dim lLastVariableRow As Long

Set CurrWks = ThisWorkbook.Worksheets("basesheet")
Set NewWb = Workbooks.Add
Set NewWks = NewWb.Worksheets(1)
lLastFixedRow = CurrWks.Range("O65536").End(xlUp).Row

For Each rng In CurrWks.Range("O2:O" & lLastFixedRow)
iLastCol = rng.Offset(0, 6).End(xlToLeft).Column
lLastVariableRow = NewWks.Range("A65536").End(xlUp).Row + 1
rng.Resize(, iLastCol - 14).Copy
NewWks.Range("A" & lLastVariableRow).PasteSpecial _
Paste:=xlPasteValues, Transpose:=True
Next rng
End Sub


Nick Hodge
Microsoft MVP - Excel
Southampton, England

On Sun, 30 Jan 2005 19:57:19 +0530, "Hari Prasadh"
wrote:

No problem, maybe I misunderstood.

Presently your code is copying data only up to the column based on row
number 2's non empty column. So, if I have data till column Q for row
number 2 and if any of the rest of the rows I have data beyond column Q ,
then data till column Q only is considered.

I don't think this is the case. It will take the range from O2 to the last
cell in the sheet
with data.

Also the macro is copying entries column wise.. That is all the non-empty
entries in
column O, then P and so on. In the present case I prefer a row wise
copying macro, which Nick has provided.

It would be easy to change to copying row-wise. I did it column-wise as I
thought that if it came
across an empty cell it would have to move to the next column.
Doing it the way I did is probably faster than copying ranges, but this may
not be important.

One syntax related doubts in your code. You have used -- With
Sheets(2) -- in your code.

The VBA help will tell you all about this. Just right-click sheets in the
object browser and pick help.


RBS



"Hari Prasadh" wrote in message
...
Hi RBS,

Thnx for your code.

Just a ..Presently your code is copying data only up to the column based
on row number 2's non empty column. So, if I have data till column Q for
row number 2 and if any of the rest of the rows I have data beyond column
Q , then data till column Q only is considered. Also the macro is copying
entries column wise.. That is all the non-empty entries in column O, then
P and so on. In the present case I prefer a row wise copying macro, which
Nick has provided.

One syntax related doubts in your code. You have used -- With
Sheets(2) -- in your code. I have never come across a numeral within
sheets(), I have seen it to be a string or a string surrounded by
quotations. Is this 2 same as the code name reference used by Excel (I
first came across this code name reference , yesterday, while browsing
through one of J Peltier's article). Is there some source where I may read
a little bit about what this code name is about.

Thanks a lot,
Hari
India

Worksheets("2") refers to a sheet that has a tab name of 2

worksheets(2) refers to the sheet that is second in the tab order

the code name has nothing to do with either of these and does not work with
the Worksheets collection.

code name is an alternate way to refer to a worksheet

Sheet1.Range("a1").Value = 3

If you look in the project explorer (in the VBE) you will see entries like

sheet1 (Jobs)

Jobs is the tab name of the worksheets and is used with the worksheets
collection. Sheet1 is the code name.

When you first open a workbook the code name and tab name probably will
match. (they do in US English anyway).

--
Regards,
Tom Ogilvy

"Hari Prasadh" wrote in message
...
Hi RBS,

Thnx for your code.

Just a ..Presently your code is copying data only up to the column based
on
row number 2's non empty column. So, if I have data till column Q for row
number 2 and if any of the rest of the rows I have data beyond column Q ,
then data till column Q only is considered. Also the macro is copying
entries column wise.. That is all the non-empty entries in column O, then
P
and so on. In the present case I prefer a row wise copying macro, which
Nick
has provided.

One syntax related doubts in your code. You have used -- With
Sheets(2) --
in your code. I have never come across a numeral within sheets(), I have
seen it to be a string or a string surrounded by quotations. Is this 2
same
as the code name reference used by Excel (I first came across this code
name
reference , yesterday, while browsing through one of J Peltier's article).
Is there some source where I may read a little bit about what this code
name
is about.

Thanks a lot,
Hari
India

Hi Tom,

I have taken a printout of your explanation. It will be part of my
*tool-box* from now on.

Thanks a lot,
Hari
India