Results 1 to 3 of 3

Thread: Checking data and copying to cell

  1. #1

    Question Checking data and copying to cell



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have data in cells a1 b1 c2 d2 a5 b5 c9 d9 as an example. If you load this into a spreadsheet and look at it, you wouldn't want to print it out looking like that. I have that problem. I need to send a spreadsheet like this to people, and I have over 100 spreadsheets to send. So, I don't want to delete the blank cells manually because they contain formulas, and it would take me many hours to do it. What I'm looking for is, a function or formula set that will look at cell a1 and if it contains any text to copy it into cell g1. Then look at the new spreadsheet without any hidden formulas in it and then delete the blank cells so it looks acceptable to email to someone. Thanks to everyone for your help with this.

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,481
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by union865 View Post
    you wouldn't want to print it out looking like that.
    So how would you want it looking!?
    Quote Originally Posted by union865 View Post
    a function or formula set that will look at cell a1 and if it contains any text to copy it into cell g1.
    OK. So that deals with cell A1, what about the rest?
    The following macro will copy values from a block (as defined in the first line) in the active sheet to a new sheet as values. On that new sheet, it will remove blanks.
    I can only guess what you want so it's probably wrong. (I notice that the ranges you quote are in pairs, so maybe you want the results in just two columns? I really don't know as I can neither look over your shoulder to see what's on your screen nor read your mind.)
    Code:
    Sub Macro7()
    SourceRangeAddress = "A1:D9"  '<< adjust this.
    Set oldSht = ActiveSheet
    Set NewSht = Sheets.Add
    NewSht.Range("A1").Resize(Range(SourceRangeAddress).Rows.Count, Range(SourceRangeAddress).Columns.Count).Value = oldSht.Range(SourceRangeAddress).Value
    NewSht.UsedRange.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
    End Sub

  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,481
    Articles
    0
    Excel Version
    365
    ps. you might use formulae to do this but they might get quite complex (I've just noticed this thread is in the Formulas section!)

Posting Permissions

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