Sub DEL95HTMLempty Cells() 'David Mc Ritchie, 2002-08-24, Worksheet Functions ' Move cells up into empty cell below if Column A cell ' on line to be moved up is empty. Replace What:=Chr(160), Replacement:=Chr(32), _ Look At:=xl Part, Search Order:=xl By Columns, Match Case:=True Rcnt = Cells. Sub Del Cells Up() 'David Mc Ritchie 1998-07-17 revised 2002-01-17 ' Empty Cells and cells with only spaces in range ' and move cells up from below even if not in range 'Will process single range of one or more columns 'Will not remove cells with formulas Application. Calculation = xl Calculation Manual 'pre XL97 xl Manual Dim rng As Range, ix As Long Set rng = Intersect(Selection, Active Sheet.
Location: these additional related macros) Home Page: refer to Getting Started with Macros.
For more depth see Install a Macro or User Defined Function on my Formula page.
When deciding which formula to use for vertical lookups, the majority of Excel gurus agree that INDEX / MATCH is far better than VLOOKUP.
However, many Excel users still resort to utilizing VLOOKUP because it's a simpler function.
For example, if the range B1: B3 contains the values "New-York", "Paris", "London", then the formula For more information about the MATCH function, please see How to use MATCH function in Excel.
At first sight, the usefulness of the MATCH function may seem questionable.This happens because very few people fully understand all the benefits of switching from Vlookup to Index Match, and without such understanding no one is willing to invest their time to learn a more complex formula.Below, I will try to point out the key advantages of using MATCH / INDEX in Excel, and then you decide whether you'd rather stick with Vlookup or switch to Index Match.This macro will move cells up if there is an empty cell above and column A cell is empty. Column For r = Rcnt To 2 Step -1 If Is Empty(Cells(Rcnt, 1)) Then For c = 1 To Ccnt If Not Is Empty(Cells(r, c)) Then If Not Is Empty(Cells(r - 1, c)) Then Go To notthis End If Next c For c = 1 To Ccnt If Not Is Empty(Cells(r, c)) Then Cells(r - 1, c) = Cells(r, c) End If Next c Cells(r, 1). Screen Updating = True End Sub The Del Cells Up macro will delete all empty cells within the highlighted range and move the cells and formatting up from below.Data at the right tests more than just what can happen with Excel 95 pasting. Calculation = xl Manual '--Excel 95 Dim Rcnt As Long, Ccnt As Long, r As Long, c As Long Dim Curr Cell As Range On Error Resume Next Selection. Cells deleted will only be deleted from within range; but cells moved up includes cells from below range. Calculation = xl Calculation Automatic Application. Screen Updating = True End Sub Remove content of cells with constants that look empty, but are not, David Mc Ritchie, 2003-07-23, worksheet.functions -- not deleting the cells which would cause cells to move up, but only remove the constants so that they are in fact EMPTY.The MATCH function determines the relative position of the lookup value in the specified range of cells.