+ Reply to Thread
Results 1 to 14 of 14

Removing spaces

  1. #1
    Forum Contributor
    Join Date
    03-12-2014
    Location
    Asheville, NC
    MS-Off Ver
    Excel 2016
    Posts
    99

    Removing spaces

    I have a series of spreadsheets, one for each month, from the past several years. I want to bring the annual data from these different sheets all together to one worksheet. I realize that putting all of the years into one workbook would crash my computer. So, annual is fine.

    However, there are different lengths in each column. Some go from 1 to 100, some go from 1 to 1000. I REALLY don't want to go through and manually copy each month onto the master sheet.

    I am trying to figure out how to copy a range of cells referentially, but nothing seems to work. I can use "xlDown", then "xlRight". It's fine if the column to the right is shorter than the current column.

    This is mind boggling!

    Here is a sample:Sample Numbers.xlsx

  2. #2
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Re: Removing spaces

    You example is an image pasted into a worksheet....
    Also, what's your expected outcome?

  3. #3
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Removing spaces

    Maybe:

    Please Login or Register  to view this content.

  4. #4
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2016
    Posts
    1,416

    Re: Removing spaces

    Make use of the UsedRange - basically UsedRange is a rectangular array the represents the smallest sheet that would contain all of the cells with formulas/values.

    Example> This would select the bottom-right cell of the used range
    Please Login or Register  to view this content.
    Parts:

    Sheet1.UsedRange.Rows.Count , is the last used row on sheet1
    Sheet1.UsedRange.Columns.Count , is the last used column on sheet1

    You could also copy out the entire used range if you like.
    Remember, saying thanks only takes a second or two. Click the star icon(*) below the post you liked, to give some Rep if you think an answer deserves it.

    Please,mark your thread [SOLVED] if you received your answer.

  5. #5
    Forum Contributor
    Join Date
    03-12-2014
    Location
    Asheville, NC
    MS-Off Ver
    Excel 2016
    Posts
    99

    Re: Removing spaces

    Also, what's your expected outcome?
    My hope is to graph these results for the year so I can follow trends easily visually.

    I'll try out the samples. Thanks!

    Here is the spreadsheet corrected: Sample Numbers.xlsx
    Last edited by TRLWNC; 08-03-2015 at 08:49 AM. Reason: Correct spreadsheet

  6. #6
    Forum Contributor
    Join Date
    03-12-2014
    Location
    Asheville, NC
    MS-Off Ver
    Excel 2016
    Posts
    99

    Re: Removing spaces

    John-I get an "Out of range" error when this goes to paste.

    I'll try again. Thanks!

  7. #7
    Forum Contributor
    Join Date
    03-12-2014
    Location
    Asheville, NC
    MS-Off Ver
    Excel 2016
    Posts
    99

    Re: Removing spaces

    How about a simpler solution-If I copy each column separately, then paste them starting at the bottom of the column on the master page.

    Can I do that referentially so I don't have to do it by hand? Surely I can loop through them, but I'm not too good with that. Hmmm....

    Going to work on that.
    Last edited by TRLWNC; 08-03-2015 at 09:22 AM.

  8. #8
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2016
    Posts
    1,416

    Re: Removing spaces

    Do you have a sheet named "Master"?

    Try this:

    Please Login or Register  to view this content.
    Your 'master' sheet's name needs to be in the code where I've indicated above (in red)
    Last edited by GeneralDisarray; 08-03-2015 at 09:20 AM.

  9. #9
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Removing spaces

    You need to create a sheet named Master. Try:

    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    03-12-2014
    Location
    Asheville, NC
    MS-Off Ver
    Excel 2016
    Posts
    99

    Re: Removing spaces

    Okay, I went through that carefully and saw that it selects the entire worksheet. That's nice to do, but I don't understand how to use that info to paste the columns onto the end of the previous information.

    I will see what I can do to use that info to loop.

    Thanks for the help!

  11. #11
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Removing spaces

    When you say end, do you want paste each sheets data below each other (last row)? Or beside each other (last column)?

  12. #12
    Forum Contributor
    Join Date
    03-12-2014
    Location
    Asheville, NC
    MS-Off Ver
    Excel 2016
    Posts
    99

    Re: Removing spaces

    At the end of the column's last row. The goal here is to have all of the data for the year (for the column in question) in one column so I can chart them easily.

    I realized from your question that I haven't given you all of the information, so I edited the spreadsheet for more clarification: Sample Numbers.xlsx

    I need the columns on the second page to be copied to the end of the columns on the first page.

    Thanks for the help!
    Last edited by TRLWNC; 08-03-2015 at 10:05 AM. Reason: Giving more information

  13. #13
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Removing spaces

    Did you test the code in Post#9? Does it help any?

  14. #14
    Forum Contributor
    Join Date
    03-12-2014
    Location
    Asheville, NC
    MS-Off Ver
    Excel 2016
    Posts
    99

    Re: Removing spaces

    Yes, I did. It gives me the column count. I am working on copying entire columns, and I'm not having much success. Thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Removing spaces
    By makinmomb in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-29-2015, 02:46 PM
  2. REmoving spaces
    By sipunmishra in forum Excel General
    Replies: 5
    Last Post: 01-10-2014, 12:55 AM
  3. Removing spaces
    By davidx in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-15-2013, 05:43 AM
  4. Excel 2007 : removing spaces
    By bjamin_w in forum Excel General
    Replies: 4
    Last Post: 02-19-2010, 09:04 AM
  5. Removing Spaces
    By jdomante in forum Excel General
    Replies: 4
    Last Post: 11-18-2009, 04:32 PM
  6. Removing Spaces
    By Solarissf in forum Excel General
    Replies: 4
    Last Post: 08-31-2006, 11:23 AM
  7. Need:removing spaces
    By kcm in forum Excel General
    Replies: 4
    Last Post: 06-01-2005, 11:49 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1