Home |
Search |
Today's Posts |
#1
|
|||
|
|||
New Questions about Recording Macros
Thanks in advance...
This is what I have I have alot of data in column A Basically they're addresses of clients The problem is they are all bunched together in column A The recipient information could have a range of 5 to 10 lines of data This is what I want to do... I want to be able to record a macro so that whatever group of cells I choose, I want to be able to copy, select the next cell above the data and paste special, then transpose. For example.. Cell A1 is blank Cells A2 through A6 have data Cells A7 and A8 are blank Cells A9 through A15 have data I want to select Cells A2 through A6, copy, select A1, paste special and traspose the data. The problem is that when I record this it says I specifically select A1. I don't want to specify any one cell, but the cell above the data so that it can be transposed. I've included the recorded macro below. The whole idea behind this is that we get these client lists all the time but they can be in text or word.doc files or xls format. Even with the Replace features we do sometimes go through hundreds of client addresses one by one, removing hard returns manually. I am trying to find a faster way to organize the lists. Which leads me to enter a manual line break (such as in the special replace option in MS WORD) With this I make sure there is a line or two between customers and past the text into excel hoping I can use a macro below. Sub Macro1() ' ' Macro1 Macro ' Macro recorded 07/12/2004 by Greegan ' ' Keyboard Shortcut: Ctrl+a ' Range(Selection, Selection.End(xlDown)).Select Selection.Copy Range("A1").Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True End Sub |
#2
|
|||
|
|||
Hey man that's exactly what I needed. Thank you very much.
G "Dave Peterson" wrote in message ... Try this against a copy of your worksheet: Option Explicit Sub testme() Dim myRng As Range Dim myArea As Range With Worksheets("sheet1") Set myRng = Nothing On Error Resume Next Set myRng = .Range("a:a").Cells.SpecialCells(xlCellTypeConstan ts) On Error GoTo 0 If myRng Is Nothing Then MsgBox "No Values in this worksheet!" Exit Sub End If For Each myArea In myRng.Areas myArea.Copy myArea.Cells(1).Offset(0, 1).PasteSpecial Transpose:=True Next myArea On Error Resume Next .Range("b:b").Cells.SpecialCells(xlCellTypeBlanks) .EntireRow.Delete .Columns(1).Delete On Error GoTo 0 End With End Sub It actually pastes the transposed data into column B, then cleans up column B and column A. Greegan wrote: Thanks in advance... This is what I have I have alot of data in column A Basically they're addresses of clients The problem is they are all bunched together in column A The recipient information could have a range of 5 to 10 lines of data This is what I want to do... I want to be able to record a macro so that whatever group of cells I choose, I want to be able to copy, select the next cell above the data and paste special, then transpose. For example.. Cell A1 is blank Cells A2 through A6 have data Cells A7 and A8 are blank Cells A9 through A15 have data I want to select Cells A2 through A6, copy, select A1, paste special and traspose the data. The problem is that when I record this it says I specifically select A1. I don't want to specify any one cell, but the cell above the data so that it can be transposed. I've included the recorded macro below. The whole idea behind this is that we get these client lists all the time but they can be in text or word.doc files or xls format. Even with the Replace features we do sometimes go through hundreds of client addresses one by one, removing hard returns manually. I am trying to find a faster way to organize the lists. Which leads me to enter a manual line break (such as in the special replace option in MS WORD) With this I make sure there is a line or two between customers and past the text into excel hoping I can use a macro below. Sub Macro1() ' ' Macro1 Macro ' Macro recorded 07/12/2004 by Greegan ' ' Keyboard Shortcut: Ctrl+a ' Range(Selection, Selection.End(xlDown)).Select Selection.Copy Range("A1").Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True End Sub -- Dave Peterson |
#3
|
|||
|
|||
Try this against a copy of your worksheet:
Option Explicit Sub testme() Dim myRng As Range Dim myArea As Range With Worksheets("sheet1") Set myRng = Nothing On Error Resume Next Set myRng = .Range("a:a").Cells.SpecialCells(xlCellTypeConstan ts) On Error GoTo 0 If myRng Is Nothing Then MsgBox "No Values in this worksheet!" Exit Sub End If For Each myArea In myRng.Areas myArea.Copy myArea.Cells(1).Offset(0, 1).PasteSpecial Transpose:=True Next myArea On Error Resume Next .Range("b:b").Cells.SpecialCells(xlCellTypeBlanks) .EntireRow.Delete .Columns(1).Delete On Error GoTo 0 End With End Sub It actually pastes the transposed data into column B, then cleans up column B and column A. Greegan wrote: Thanks in advance... This is what I have I have alot of data in column A Basically they're addresses of clients The problem is they are all bunched together in column A The recipient information could have a range of 5 to 10 lines of data This is what I want to do... I want to be able to record a macro so that whatever group of cells I choose, I want to be able to copy, select the next cell above the data and paste special, then transpose. For example.. Cell A1 is blank Cells A2 through A6 have data Cells A7 and A8 are blank Cells A9 through A15 have data I want to select Cells A2 through A6, copy, select A1, paste special and traspose the data. The problem is that when I record this it says I specifically select A1. I don't want to specify any one cell, but the cell above the data so that it can be transposed. I've included the recorded macro below. The whole idea behind this is that we get these client lists all the time but they can be in text or word.doc files or xls format. Even with the Replace features we do sometimes go through hundreds of client addresses one by one, removing hard returns manually. I am trying to find a faster way to organize the lists. Which leads me to enter a manual line break (such as in the special replace option in MS WORD) With this I make sure there is a line or two between customers and past the text into excel hoping I can use a macro below. Sub Macro1() ' ' Macro1 Macro ' Macro recorded 07/12/2004 by Greegan ' ' Keyboard Shortcut: Ctrl+a ' Range(Selection, Selection.End(xlDown)).Select Selection.Copy Range("A1").Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True End Sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macros making file saving extremely slow. | Excel Discussion (Misc queries) | |||
Transferring toolbars and macros to other computers | Excel Discussion (Misc queries) | |||
recording macros | New Users to Excel | |||
The available macros list in XL; how to suppress filename from showing | Excel Discussion (Misc queries) | |||
Macros disappear after a file is imported | Excel Discussion (Misc queries) |