Cut not Copy Appended Data

In this great Macro, created by Micky (MVP), is there a way to cut and paste 
the data from all the columns instead of copy and paste? I just want all the 
appended data in Column A".

Sub Tony()
    Application.ScreenUpdating = False
    LR = ActiveSheet.UsedRange.Rows.Count
    LC = ActiveSheet.UsedRange.Columns.Count
    Ind = LR + 1
    For C = 2 To LC
        For R = 1 To LR
            If Cells(R, C) <> "" Then
               Cells(Ind, 1) = Cells(R, C)
               Ind = Ind + 1
            End If
        Next
    Next
    Application.ScreenUpdating = True
End Sub

0
Utf
4/1/2010 3:50:01 PM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
960 Views

Similar Articles

[PageSpeed] 51

If the order the values are pasted into column 1 don't matter (by rows
instead of by columns), then you can use:


Code:
--------------------
  

  
Sub Tony()
  Dim Ind As Long, LR As Long, LC As Long
  Application.ScreenUpdating = False
  LR = ActiveSheet.UsedRange.Rows.Count
  LC = ActiveSheet.UsedRange.Columns.Count
  Ind = LR + 1
  For Each ce In Range(Cells(1, 2), Cells(LR, LC))
  If ce.Value <> "" Then
  ce.Cut Cells(Ind, 1)
  Ind = Ind + 1
  End If
  Next ce
  Application.ScreenUpdating = True
  End Sub
--------------------



If order matters (need to go down each column, then across), try:


Code:
--------------------
  

  
Sub Tony2()
  Dim Ind As Long, LR As Long, LC As Long
  Dim C As Long, R As Long
  Application.ScreenUpdating = False
  LR = ActiveSheet.UsedRange.Rows.Count
  LC = ActiveSheet.UsedRange.Columns.Count
  Ind = LR + 1
  
  For C = 2 To LC
  For R = 1 To LR
  If Cells(R, C).Value <> "" Then
  Cells(R, C).Cut Cells(Ind, 1)
  Ind = Ind + 1
  End If
  Next R
  Next C
  Application.ScreenUpdating = True
  End Sub
--------------------


-- 
Paul

- Paul
------------------------------------------------------------------------
Paul's Profile: 1697
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=192265

http://www.thecodecage.com/forumz

0
Paul
4/1/2010 8:46:50 PM
Great job Paul... that did it.
Thanks!

"Paul" wrote:

> 
> If the order the values are pasted into column 1 don't matter (by rows
> instead of by columns), then you can use:
> 
> 

> Code:
> --------------------
>   
> 
  
> Sub Tony()
>   Dim Ind As Long, LR As Long, LC As Long
>   Application.ScreenUpdating = False
>   LR = ActiveSheet.UsedRange.Rows.Count
>   LC = ActiveSheet.UsedRange.Columns.Count
>   Ind = LR + 1
>   For Each ce In Range(Cells(1, 2), Cells(LR, LC))
>   If ce.Value <> "" Then
>   ce.Cut Cells(Ind, 1)
>   Ind = Ind + 1
>   End If
>   Next ce
>   Application.ScreenUpdating = True
>   End Sub

> --------------------
> 
> 
> 
> If order matters (need to go down each column, then across), try:
> 
> 

> Code:
> --------------------
>   
> 
  
> Sub Tony2()
>   Dim Ind As Long, LR As Long, LC As Long
>   Dim C As Long, R As Long
>   Application.ScreenUpdating = False
>   LR = ActiveSheet.UsedRange.Rows.Count
>   LC = ActiveSheet.UsedRange.Columns.Count
>   Ind = LR + 1
>   
>   For C = 2 To LC
>   For R = 1 To LR
>   If Cells(R, C).Value <> "" Then
>   Cells(R, C).Cut Cells(Ind, 1)
>   Ind = Ind + 1
>   End If
>   Next R
>   Next C
>   Application.ScreenUpdating = True
>   End Sub

> --------------------
> 
> 
> -- 
> Paul
> 
> - Paul
> ------------------------------------------------------------------------
> Paul's Profile: 1697
> View this thread: http://www.thecodecage.com/forumz/showthread.php?t=192265
> 
> http://www.thecodecage.com/forumz
> 
> .
> 
0
Utf
4/2/2010 4:38:01 PM
Reply:

Similar Artilces:

Short cuts?
Is there a way to get excel in a macro to put a shortcut to the current file on to the desktop? Thanks in advance Greg Hi Greg, Here is some code Sub CreateShortCut() Dim oWSH As Object Dim oShortcut As Object Dim sPathDeskTop As String Set oWSH = CreateObject("WScript.Shell") sPathDeskTop = oWSH.SpecialFolders("Desktop") Set oShortcut = oWSH.CreateShortCut(sPathDeskTop & "\" & _ ActiveWorkbook.Name & ".lnk") With oShortcut .TargetPath = ActiveWorkbook.FullName .Save End With Set oWSH = Nothing...

Error in copying HTML Table to Excel
I have Excel 97 (english version) installed on my WIN NT and when I try to COPY a table from IE6.0 (HTML) which uses Greek encoding, and PASTE into Excel I get undefined characters. ...

how to do % of cells with data
My sheet looks like this: _ABCDEF____ 1x_x_______ 2x_x_______ 3x_x_______ 4x_x_______ 5x_________ 6x_________ 7x_________ 8x_________ I want to know the % of cells in column C that have data in them, and display this % in E1. Column A tells us we have 8 rows, so E1 then would display 50%. -- matthewst ------------------------------------------------------------------------ matthewst's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=20340 View this thread: http://www.excelforum.com/showthread.php?threadid=483610 Hi Matthew Try =COUNTIF(C1:C8,"=x")/C...

CRM 4.0 Data Migration Manager
Hi I have an accounts csv file with account number (from old system), account name address etc. I also have a contacts file which has the account number, contact name etc. There are a lot of accounts with exctly the same name but different number and details. I want to use the crm 4.0 data migration manager to import these and link the contacts to the accounts. Have tried a few times and i keep getting the error saying - duplicate reference could not be resolved. How do i go about this please? I would relaly appreciate some guidance.. Cheers Snow White Hi, I've had a similar si...

Backup Copies
How do I disable the creation of the backup copy? Created in 97, used thru 2000 & 2003. Deleting file just causes a new one to be created next time I run spreadsheet. Hi Tony, Don't confuse backup with Excel's requirement to maintain two copies of a file while working on it. If this involves diskettes you should be aware that you should never access removeable media directly from Excel or risk loss. You might wish you didn't stop creating a backup copy (.XLK) for your file, but Save As (F12 key), then on the Save As Dialog menus: Tools --> General Options, *uncheck*...

Sorting my data is changing my data.. HELP!
Hey out there! First let me tell you what I'm doing. I am running a Poker tournament where we have 400+ players. We have presold tickets and I have entered all the data into a spreadsheet in Excel 2000. It looks a little something like this: NUMBER RANDOM FIRST LAST TABLE SEAT 1 0.188690868 HUGH WILLIAMS 1 1 2 0.929280841 BEN BAGWELL 1 2 3 0.473961247 DEL PETERON 1 3 4 0.815368374 SAM CHAMBER 1 4 5 0.776510718 RICK FETHER 1 5 6 0.92670956 DAVID WEST 1 6 7 0.508697966 MARK MONCRIEF 1 7 8 0.152206582 PAUL ROPE 1 8 9 0.43782459 JACK COSTELLO 2 1 10 0.878867598 ...

Money Loses Reconciliation Data on Restore???
This is the second time this has happened to me. :( I had to restore ms money 2005 from a backup after moving the program to another computer or reinstalling the program after wiping a computer. I very carefully backed up the data first. It's all there, all except the reconciliation data! It looks like I've never reconciled my bank at all! What am I doing wrong? It's bad enough I can't seem to figure out how to print a reconciliation report showing uncleared transactions, etc., after performing a reconciliation. Thanks for any suggestions. In microsoft.public.money, C...

Copying Graphs to Word
Using Office XL. I am copying graphs from Excel to a Word document. The copy command does pick-up the graph area and pastes over to Word. However, consistently it will drop one or two legends/text boxes in the copying process. Which one it drops is not consistent. The rest of the graph will be great and pops right in. Any suggestions? I can be corresponded to directly at this newsgroup or e-mail. Have you tried Edit|Paste Special and specified Picture (Metafile)? The chart will be static (not linked to Excel workbook). Bernard "Jim" <tpj0001@aol.com> wrote in me...

PDK (Personal Data Keeper) Form Modification
Is it possible to modify the form for entering a timesheet in PDK? I would like to have the project name as well as the project number display on the time entry screen. Currently only project number displays on the timesheet form, although you can see the project name in the related look up. I would like to hear from anyone who uses PDK about how to make the product simpler to use, or what product you are using in lieu of PDK to track time. My e-mail address is lrivers@worknetinc.com. ...

Excell
Can anyone help - Im finding that if i Cut an entire row which has cells in it that hve Conditional Formatts and Insert that row elsewhere in a workbook that the conditional formatts get all messed up, this seems to be a glich with Excel 2000 - does any one know of any fixes - it doesnt do the same think in Excel XP. Please email me if you can help or know how i can ask microsoft direct without it costing me a fortune Jim, My guess is that the Conditional Formatting is using a formula rather than a cell value. It may be the relative/absolute cell references in the formulas. Give de...

A chart that allows for new data to be entered
I have a set of data that I need to put into a simple line graph. need the chart to recognize and add new data points into its data se as I enter the new values. I have tried to extend the data range by another 40-50 empty cells s that they will automatically be included when data is entered. Th graph keeps coming up basically blank. If anyone knows a fix for this, please let me know. Thanks, Wil -- Message posted from http://www.ExcelForum.com Will - You can set up a dynamic chart, based on a dynamic range that updates as data is added. For a few examples and a lot of links, check ...

copy bitmap to Lotus Notes and email
Hi, I am trying to copy a range of cells as a bitmap file and copy that file into Lotus Notes using my macro. Can anyone help me? This is the offending code: Range("A1:L36").Select Selection.CopyPicture Appearance:=xlScreen, Format:=xlBitmap bodytext = ActiveSheet.Paste At the moment the variable bodytext represents the data I want to appear in the message. I adapted the code from elsewhere where this was defined as a string, but i changed it to a variant as string and bitmap are probably not compatible. Help help please! cheers, David ...

Migrate Exchange Data #2
Dear All We have a SBS 2003 Server. It seems that DNS and AD is corrupted, so I wish to move the Exchange Data to anohter computer, install the SBS and move back the Exchange data (because we only need the exchange data with all the user names etc - other data like the sharepoint etc. is not important). Which is the most easiest way to accomplish this? thanks & best regards Pradeep In news:101250D5-C3A9-4C6B-B479-C7648E76BB5C@microsoft.com, Avir <Avir@discussions.microsoft.com> typed: > Dear All > > We have a SBS 2003 Server. It seems that DNS and AD is corrupte...

Text being cut off
Hello - I have a portrait folded card (tent card) that I am using to make place settings. The name of the person is in a text box at the bottom of the page as normal, then at the top the text box is rotated so that the name is upside down -this way when the card is folded in half the name is rightside up on both sides. The text in the box that is upside down is being barely cut off when I print, just as if the top third is being shaved away straight across. Is this a publisher issue because the text is flipped or could it be my printer? thank you! It is the limitation of your printer...

Problem Copying Charts to PowerPoint
I have some Excel code that copies some Excel charts to PowerPoint. It's giving me an error message that the clipboard is empty. Something in my code must be emptying the clipboard, but I don't see what it is. The error message occurs when I try to paste the chart at the last line of the code shown below: Range("Analysis_Left").CopyPicture Appearance:=xlScreen, Format:=xlPicture 'Open PowerPoint Set myAPP = CreateObject("Powerpoint.Application") myAPP.Visible = True myAPP.WindowState = ppWindowMinimized ThisWorkbook.A...

moving range of data listed up/down to across
Hi, I have a range of data entered in range A:1 thru A:26 I would like to move the data so it appears on one long line going across instead , say line 1 columns A - Z. I am using excel version 97. Thanks for your help. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ If you copy A1:A26 using paste special, check the box Transpose and this will copy the data across the sheet. Note you should copy this to B1 and then delete column A if this is no longer required Ho...

Populate Word template from contact data
Is there a way to populate portions of a Word template from contact data? Specifically, I want to create a template that will automatically fill in contact information, then allow the user to tab to and manually fill in other fields. Shannon You can use mail merge for that. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "Shannon" <Shannon@discussions.microsoft.com> wrote in message news:E8553A13-7206-4767-952F-A8E4B09BD965@microsoft....

Copy Security Task , Role from Installation to another In GP10
Hello I had test upgrade from GP 9 to GP 10 on test environment, then I setup some security roles and tasks, Now i need to upgrading in live server ..... the question here is: Is there a way to copy "Security Task ,Security Role and Alternate Modified/Forms and Reports" from Company to another company? such as Export All security from company then Import it in other company?? There are a number of security copy functions that are not provided in v10. You cannot, for example, copy a user's security to a new user. You can copy one user's company security to another com...

What files to copy
Hi all, I having some problems with my PC. (some type of virus I suspect). So I want to copy my Money files to a another HDD before I fdisk my boot drive. My backup copy is old. What files do I need to copy and where would I find them. I would go to the Microsoft FAQ's but no apps will actually start on the PC and I don't have web access here at work. Thanks. Hi Mr. InOnly Do a search for *.mny, and that should find all your money files and then you can select the ones you want. Bob "Mr. 1nOnly" <mr_1nonlyNO_SPAM@hotmail.com.remove> wrote in message news:_ixlb.3...

cut & paste with short cut keys (ctrl C V) with absolute cell refe
How do I cut & paste in Excel with short cut keys (ctrl C V) but maintain absolute cell references rather than it always keeping relative references Hi use a cell reference such as $A$1 -- Regards Frank Kabel Frankfurt, Germany "Modeller Dave" <Modeller Dave@discussions.microsoft.com> schrieb im Newsbeitrag news:285C1268-78E0-4BF9-80C6-D6D7DE97ACDD@microsoft.com... > How do I cut & paste in Excel with short cut keys (ctrl C V) but maintain > absolute cell references rather than it always keeping relative references ...

Sorting data help
Apologies if this is posted to the wrong group. I have a worksheet that has 5 columns of data that I would like for other users to be able to sort by clicking on a button or drop down. I assume this will be a VBA function. Can someone point me to a reference that will help me do this? Regards, CS Maybe this stuff at Debra Dalgleish's site: http://www.contextures.com/xlSort02.html Conner Stevens wrote: > > Apologies if this is posted to the wrong group. I have a worksheet that has > 5 columns of data that I would like for other users to be able to sort by > clicking on a...

Using excel as a data base
How to create names and addresses in EXCEL that can be used in a mail merge in Word One row per record. Each field in a separate cell/column. It's much easier to combine fields than it is to separate them correctly. You may want to read some tips for mailmerge: http://www.mvps.org/dmcritchie/excel/mailmerg.htm http://www.mvps.org/word/FAQs/MailMerge The first is from David McRitchie and the second is by Beth Melton and Dave Rado. Nan wrote: > > How to create names and addresses in EXCEL that can be used in a mail merge > in Word -- Dave Peterson ...

Wrap/Picture/Copy Text/Background
Hello I have a few questions: 1. Is there a version of Publisher that had NO Text Wrap in the Picture Toolbar? 2. Is it possible to send a picture back or forward in Publisher as there is in Words Drawing Toolbar? Did any version of Publisher have a Drawing Toolbar like Word? 3. When copying text from the internet, sometimes the text can be inserted in an existing empty Text Box (like in a brochure template) and other times it creates it's own Text Box outside in the Work Area. Why? 4. When inserting ClipArt, is there any way to change the background of the ClipArt's box to...

Memo field last line cut off in print preview, not in report view
I have created a report that includes a memo field. Can grow is enabled. In Report view, it is fine, shows all the memo field info. However, in print preview, it cuts off the last line. Access 2007 Any help would be greatly appreciated. Rick ...

how to append text to the end of a row
I have to batch rename a few thousand images that are named something like 1-1234-1111-1.jpg A program I have can rename using a csv file with 2 columns originalname;newname 1-1234-1111-1.jpg;978-1-1234-1111-1.jpg This works awesome, however it has to include the extension because otherwise it can't match up the files... which is good because its possible that there could be files with the same name but different extension. I can easily open the csv in notepad and find replace ; with .jpg; but I don't know how to do that for the 2nd row. There is a space after the last characte...