deleting blank cells from a list

N

nelsok

Board Regular
Joined
Jan 20, 2006
Messages
166
I am currently using this formula to get ride of blank cells in a list and create a new list with no blanks.


does anyone know a shorter formula?


=IF(ROW()-ROW(NoBlanksRange)+1>ROWS(BlanksRange)-
COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL(
(IF(BlanksRange<>"",ROW(BlanksRange),ROW()+ROWS(BlanksRange))),
ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
Hi, nelsok,

would code be an option ?
this is one example using autofilter

Code: 
Option Explicit

Sub FilterForNonBlanks()
'Erik Van Geit
'060518

Const SC As Integer = 1 'Source Column
Const TC As Integer = 2 'Target Column
Const FR As Long = 2    'First Row

Application.ScreenUpdating = False
Range(Cells(FR, TC), Cells(Rows.Count, TC).End(xlUp)).ClearContents

    With Range(Cells(FR, SC), Cells(Rows.Count, SC).End(xlUp))
    .AutoFilter Field:=1, Criteria1:="<>"
    .Copy Cells(FR, TC)
    .AutoFilter
    End With

Application.ScreenUpdating = True

End Sub
kind regards,
Erik
 
Upvote 0
Hi

This assumes that the data is in the range C1:C7 and includes blanks. The formula is array entered (Crtl, Shift, Enter) and starts in row 1. Adjust for the requried ranges and copy down.

HTH

Tony
 
Upvote 0
Hi

This assumes that the data is in the range C1:C7 and includes blanks. The formula is array entered (Crtl, Shift, Enter) and starts in row 1. Adjust for the requried ranges and copy down.

HTH

Tony
Click to expand...


which formula are you refering to?

I do not see a reference to c1:c7 in my formula or in erik.van.geit's
 
Upvote 0
Tony probably forgot to paste the formula ...
Mine is not a formula: did you try it and would it be an option ?

EDIT:
start the Visual Basic Editor (via Menu Tools, Macro or press ALT+F11).
On the Insert menu in the VBE, click Module. (if necessary)
In the module (the white area at the right), paste your code:

to run the code
click anywhere in the code and hit function key F5
or
via Excel menu: Tools / Macro / Macros (or hit Alt+F8)

assign shortcutkey
menu Tools/Macro/Macros
select your macro
click options
choose a character as shortcut: example T
to run the macro press Ctrl+Shift+T
 
Upvote 0
Book2
ABCDE
107
2ListIdxNew List
3212
4424
5636
6 9
794X
8  Y
9X5Z
10Y6 
11 
12Z7
13
Sheet1


B1 must house a 0.

B3, copied down:

=IF(A3="","",LOOKUP(9.99999999999999E+307,$B$1:B2)+1)

D1:

=LOOKUP(9.99999999999999E+307,B1:B12)

D3, copied down:

=IF(ROWS($D$3:D3)<=$D$1,LOOKUP(ROWS($D$3:D3),$B$3:$B$12,$A$3:$A$12),"")
 
Upvote 0
Hi

Here is that missing formula.....

Code: 
=IF(ROW()> COUNTA($C$1:$C$7),"",INDEX($C$1:$C$7,SMALL(IF(NOT(ISBLANK($C$1:$C$7)),ROW($C$1:$C$7),9.99E+307),ROW())))


Tony
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
Formula to look upwards in column and return value below first blank cell encountered
Replies
8
Views
1K
GTS
G
L
  • Question
Make a list removing those containing "Blank"
Replies
1
Views
126
londa_vba
L
SaraWitch
  • Solved
Formula not calculating if blank cells
2 3 4
Replies
36
Views
723
Fluff
Fluff
K
  • Question
Trouble with moving average test
Replies
3
Views
261
Kauri
K
G
  • Solved
Skins Game Conditional Formatting
2
Replies
16
Views
926
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,788
Messages
6,121,588
Members
449,039
Latest member
Arbind kumar

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top