Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Macro Help
I'm using Office 98
I have written a macro to take rows of data and make it columns of data. I am doing this one group at a time. This is the macro I am using. Keyboard Shortcut: Ctrl+q ' Selection.Copy Range("A13500").End(xlUp).Offset(1, 0).Select Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _ , Transpose:=True End Sub My issue is that I have several rows of data, with a blank space inbetween each section; rather than doing it one section at a time, is there a way to edit the macro to read a blank space and make the next section of data occur in a new row. Example: Currently I take Name Address Phone with my macro it does a transpose, to make it look like this Name Address Phone I do this one section at a time. If I have the following: Name1 Address1 Phone 1 <blank line Name 2 Address2 Phone 2 Can I make it look like this Name 1 Address 1 Phone 1 Name 2 Address 2 Phone 2 without having to do each section individually? Thank you. |
#2
|
|||
|
|||
BDK,
Use the macro below. Select the cells first, then run it. Assumes that the blanks are really blank. HTH, Bernie MS Excel MVP Sub TryNow() Dim myArea As Range For Each myArea In Selection.SpecialCells(xlCellTypeConstants).Areas myArea.Copy Range("A13500").End(xlUp).Offset(1, 0) _ .PasteSpecial Paste:=xlAll, _ Operation:=xlNone, _ SkipBlanks:=False, _ Transpose:=True Next myArea End Sub "BDK" wrote in message ... I'm using Office 98 I have written a macro to take rows of data and make it columns of data. I am doing this one group at a time. This is the macro I am using. Keyboard Shortcut: Ctrl+q ' Selection.Copy Range("A13500").End(xlUp).Offset(1, 0).Select Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _ , Transpose:=True End Sub My issue is that I have several rows of data, with a blank space inbetween each section; rather than doing it one section at a time, is there a way to edit the macro to read a blank space and make the next section of data occur in a new row. Example: Currently I take Name Address Phone with my macro it does a transpose, to make it look like this Name Address Phone I do this one section at a time. If I have the following: Name1 Address1 Phone 1 <blank line Name 2 Address2 Phone 2 Can I make it look like this Name 1 Address 1 Phone 1 Name 2 Address 2 Phone 2 without having to do each section individually? Thank you. |
#3
|
|||
|
|||
BDK
If data is consistent as per your example. Public Sub AddressSort() last = ActiveSheet.UsedRange.Row - 1 + _ ActiveSheet.UsedRange.Rows.Count With Worksheets(1) For x = last To 2 Step -1 If .Cells(x, 1).Value < "" And _ Cells(x - 1, 1).Value < "" Then Range(.Cells(x, 1), .Cells(x, 10)).Copy _ Destination:=.Range(.Cells(x - 1, 2), .Cells(x - 1, 2)) Rows(x).Delete End If Next x Range("A:A").SpecialCells(xlCellTypeBlanks).Entire Row.Delete End With End Sub Gord Dibben Excel MVP On Thu, 16 Dec 2004 06:53:03 -0800, BDK wrote: I'm using Office 98 I have written a macro to take rows of data and make it columns of data. I am doing this one group at a time. This is the macro I am using. Keyboard Shortcut: Ctrl+q ' Selection.Copy Range("A13500").End(xlUp).Offset(1, 0).Select Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _ , Transpose:=True End Sub My issue is that I have several rows of data, with a blank space inbetween each section; rather than doing it one section at a time, is there a way to edit the macro to read a blank space and make the next section of data occur in a new row. Example: Currently I take Name Address Phone with my macro it does a transpose, to make it look like this Name Address Phone I do this one section at a time. If I have the following: Name1 Address1 Phone 1 <blank line Name 2 Address2 Phone 2 Can I make it look like this Name 1 Address 1 Phone 1 Name 2 Address 2 Phone 2 without having to do each section individually? Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moving down one row in a macro | New Users to Excel | |||
Zip Code Macro | Excel Worksheet Functions | |||
Macro and If Statement | Excel Discussion (Misc queries) | |||
Macro Formula revision? | Excel Worksheet Functions | |||
Macro for multiple charts | Excel Worksheet Functions |