Announcement

Collapse
No announcement yet.

Delete empty cells and shift up

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Delete empty cells and shift up

    Hi.

    Could anyone help me with a vba script that:

    1. Goes through colomn A (a2:a353)
    2. Finds and deletes any empty or empty-looking cells
    3. Shifts-up
    4. do the same for colomn B, C and D (same range as A)


    Nic

  • #2
    Re: Delete empty cells and shift up

    Hi,

    Try deleting a cell, moving down and deleting another. Do this whilst recording the macro and you have the bare bones of your code.

    Place this in a for next loop but I would suggest going from i= 353 to 1 step -1

    Use RC references i.e. activesheet.cells(2,4) and you can replace the 2 or the 4 with i or j for doing the five columns.

    You are testing to see if activesheet.cells(2,4).value = ""

    I HTH

    John

    Comment


    • #3
      Re: Delete empty cells and shift up

      Far more efficient to use SpecialCells method.

      EG

      Option Explicit

      Sub DeleteBlanks()
      Dim intCol As Integer

      For intCol = 1 To 3
      Range(Cells(3, intCol), Cells(353, intCol)). _
      SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
      Next intCol
      End Sub
      Kind Regards, Will Riley

      LinkedIn: Will Riley

      Comment


      • #4
        Re: Delete empty cells and shift up

        I tried to run that but got a 400 error

        Nic

        Comment


        • #5
          Re: Delete empty cells and shift up

          Hi John,

          thanks for your reply.

          The thing is: "i'm a simply peasant" and you have lost me.

          I know how cut'n'paste and run and slightly modify a macro, but thats where my expertice ends

          Comment


          • #6
            Re: Delete empty cells and shift up

            Originally posted by WillR
            Far more efficient to use SpecialCells method.

            EG

            Option Explicit

            Sub DeleteBlanks()
            Dim intCol As Integer

            For intCol = 1 To 3
            Range(Cells(3, intCol), Cells(353, intCol)). _
            SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
            Next intCol
            End Sub
            Works fine for me. Having reread your post, change the code to

            Option Explicit

            Sub DeleteBlanks()
            Dim intCol As Integer

            For intCol = 1 To 4 'cols A to D
            Range(Cells(2, intCol), Cells(353, intCol)). _
            SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
            Next intCol
            End Sub


            If you get an error, please let me know which line it errors on & what the exact error message is.
            Kind Regards, Will Riley

            LinkedIn: Will Riley

            Comment


            • #7
              Re: Delete empty cells and shift up

              same error massage.

              Just 400 nothing else.

              Do I have to select the range I want to use the macro on?
              The cells are not quit empty, but filled with a formula - does that matter?

              Nic

              Comment


              • #8
                Re: Delete empty cells and shift up

                Originally posted by vestlink
                same error massage.

                Do I have to select the range I want to use the macro on?
                no

                Originally posted by vestlink
                The cells are not quit empty, but filled with a formula - does that matter?

                Nic
                Well they are either empty or not As per your original post, my code only deletes empty cells - I hadn't got a clue what you meant by "Empty Looking" If the cells are not empty, you will need to loop through each one in turn, testing for either "emptiness" or formulas that return nothing...

                I still have no idea why you get a '400 error' - ike I said, it works fine on my sample data. Perhaps you could upload a small sample for me to test on.
                Kind Regards, Will Riley

                LinkedIn: Will Riley

                Comment


                • #9
                  Re: Delete empty cells and shift up

                  Heres the example i'm working on

                  Nic
                  Attached Files

                  Comment


                  • #10
                    Re: Delete empty cells and shift up

                    there are no formulas ? I thought you said you had formulas ?
                    Kind Regards, Will Riley

                    LinkedIn: Will Riley

                    Comment


                    • #11
                      Re: Delete empty cells and shift up

                      Well, I got it working.

                      All I had to do was:

                      1. Copy the data in ind.
                      2. Paste it in a new sheet
                      3. Save it as a text-file.
                      4. Re-open the text-file.
                      5. Run the macro.

                      But it it is a bit cumbersome.

                      Even though I tried to copy and paste only the values, the blank cells wheren't blank. How come?

                      Thanks a million for your help.

                      But the non-emptiness of empty cells still puzzles me.

                      Nic

                      Comment


                      • #12
                        Re: Delete empty cells and shift up

                        Try this code on your original data.


                        Sub DeleteBlanks2()
                        Dim lRow As Integer
                        Dim intCol As Long
                        Dim rngCell As Range, fn

                        Set fn = Application.WorksheetFunction
                        Application.ScreenUpdating = False
                        For intCol = 1 To 4
                        For lRow = 353 To 2 Step -1
                        Set rngCell = Cells(lRow, intCol)
                        With rngCell
                        .Value = fn.Substitute(rngCell.Value, Chr(160), Chr(32))
                        .Value = Trim(rngCell.Value)
                        End With
                        If Len(rngCell) = 0 Then
                        rngCell.Delete shift:=xlUp
                        End If
                        Set rngCell = Nothing
                        Next lRow
                        Next intCol
                        Application.ScreenUpdating = True
                        End Sub


                        It worked on the data you sent me. I adapted it from Aaron's code here

                        the explanation re your "printable blanks" is

                        Originally posted by Aaron Blood
                        There are actually 2 different kinds of printable blank spaces. I came across something similar to what you describe while trying to process data that came from SAP.

                        Char 32 = Space
                        Char 160 = No Break Space

                        Trim works fine for stripping character 32. However, it does NOT remove the printable 160.

                        So what is this No Break Space you may ask? Turns out that in some languages, there are words and parts of speach that are seperated by a space but intended to be analyzed as a single word. Screws up the spell/grammar checkers if you use a standard space. So, a "No Break Space" is used to let the computer know it's to be treated as a single word.

                        Turns out the Germans who developed SAP thought it would be a good idea to use no break spaces in their data dumps to text files.
                        The code is substantially longer due to the loops & multiple tests it has to do, but probably quicker than the textfile solution.
                        Kind Regards, Will Riley

                        LinkedIn: Will Riley

                        Comment


                        • #13
                          Re: Delete empty cells and shift up

                          Thank you again.

                          It worked great.

                          I find it strange though, that such a functionality isn't a part of Excel as a standard.

                          It can't be the first time that this need has occured.

                          Nic

                          Comment


                          • #14
                            Re: Delete empty cells and shift up

                            Originally posted by vestlink
                            I find it strange though, that such a functionality isn't a part of Excel as a standard.
                            It can't be the first time that this need has occured.
                            Well, to be honest, deleting blank cells is part of Excel as standard. Make your selection, press F5 (or from the menu, Edit>Goto) press the Special button & check the checkbox labelled Blanks - then you can delete the blank cells.

                            Your issue is somewhat different, i.e. 'no break spaces' that generally occur when data is imported from other (generally non-office) systems. For the 1% of Excel users who get beyond SUM functions (I kid you not - we're in the minority here ) this is obviously frustrating, but I guess if you tried to cover every eventuality you would never release any software

                            Even in my 2 years at Ozgrid, this issue has only ever come up a handful of times over around 80,000 posts!!

                            Keep the solution in your list of favourites is all I can advise if you deal with this sort of data alot.....
                            Kind Regards, Will Riley

                            LinkedIn: Will Riley

                            Comment

                            Working...
                            X