Prev: How to open a password protected powerpoint file by Excel macro
Next: EOMONTH compatibility issue (workaround?)
From: Prashant on 5 Jun 2010 06:18
I have a excel sheet in which there is data in only one column. The data is
spread in 2 or 3 rows and after that there is blank row. The data spread in
these row is related to one row. I want to bring this spread data in one row.
Blank row can be as it is between ro useful data rows.
From: Dave Peterson on 5 Jun 2010 06:39
Maybe a macro would do it:

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim DestCell As Range
Dim BigArea As Range
Dim SmallArea As Range

Set CurWks = Worksheets("Sheet1")
Set NewWks = Worksheets.Add
Set DestCell = NewWks.Range("A1")

With CurWks
Set BigArea = Nothing
On Error Resume Next
Set BigArea = .Columns(1).Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0

If BigArea Is Nothing Then
MsgBox "No constants in column A"
Exit Sub
End If

For Each SmallArea In BigArea.Areas
SmallArea.Copy
DestCell.PasteSpecial Transpose:=True
Set DestCell = DestCell.Offset(1, 0)
Next SmallArea
End With

End Sub

This will not do what you want if you have any formulas in column A.

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

Prashant wrote:
>
> I have a excel sheet in which there is data in only one column. The data is
> spread in 2 or 3 rows and after that there is blank row. The data spread in
> these row is related to one row. I want to bring this spread data in one row.
> Blank row can be as it is between ro useful data rows.

--

Dave Peterson
 | 
Pages: 1
Prev: How to open a password protected powerpoint file by Excel macro
Next: EOMONTH compatibility issue (workaround?)