Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merging of rows.
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merging of rows.
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 he 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Merging Rows | Excel Programming | |||
Merging rows | Excel Programming | |||
Is merging rows possible? | Excel Worksheet Functions | |||
Merging rows | New Users to Excel | |||
Merging Two Rows Into One | Excel Worksheet Functions |