Delete blank cells

[joh46k]

I have sheet called ALL ,In that sheet i need to delete a blank cells in the column.

EXAMPLE:
this is my formula on F column =IFERROR(VLOOKUP(D9098,YC55!A:D,4,0),"")


How to do it ? pls help me !

[XOR LX]

Hi,

Sorry - what's the connection between the cell you need to delete and this formula?

Regards

[joh46k]

actually no connection just a example to show that formula returns me a blank cell instead of empty cell , In my sheet from column F to k have some formula like 1# . In a column ,some rows have blank cells but i don't want that blank cells.

[XOR LX]

Not sure I understand. That formula is being told to put a blank in the cell if the result of the VLOOKUP is an error (a match is not found, for example). Are you saying that you would like a value different from a blank to be entered in these cases?

Regards

[joh46k]

sorry ,you can actually just ignore that formula . no confusion

just help me on how to delete blank cells in a sheet . Thank you

[XOR LX]

Depends what you mean. Do you mean delete all null characters from cells (i.e. "" entries, perhaps as the result of formulas) so that they are 'truly' blank? Do you mean delete all rows which contain at least one blank cell? All columns?

Please be a bit more specific.

Regards

[joh46k]

yes ,you are right. delete all null characters from cells (i.e. "" entries, perhaps as the result of formulas) so that they are 'truly' blank

[HaHoBe]

Hi, joh46k,

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Ciao,
Holger

[XOR LX]

And I assume that these cells no longer contain the formulas (i.e. have been pasted as values, leaving a "" to remain in the cell)? Otherwise, if the formulas are still in the cell, we will be deleting those as well.

Regards

[joh46k]

Hi, joh46k,

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Ciao,
Holger

Why if i copy this code into module, it is not working ? It is keep running not coming to stop

[HaHoBe]

Hi, joh46k,

Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.

Arlette was kind enough to moderate 2 of your posts accordingly.

I donīt know what else in your workbook may be triggered as an event - maybe you should post a sample for that to have a look at. Both macros did fine in my test so itīs hard to tell why they should lead to an endless loop in your workbook (except for maybe events behind the sheet).

Ciao,
Holger

[HaHoBe]

Hi, joh46k,

while being out for lunch I thought about your concern with

It is keep running not coming to stop

In the sample I developped I had only a small amount of data in one column and very few formulas all showing "" as a result. If you have made heavy use of formulas in the sheet the calculation after any altering might be a reason for the extreme long running time. Another one might be events that will get fired once any cell within a range gets altered (and they will get altered if you replace a formula with "").

Both reasons might be overcome by altering the first code to

Please Login or Register  to view this content.

And the last reason for a long running time is when you have used formulas way beyond the used data range in preparation of the future. Both methods I had given you code for will not take care for the used data range but for the cells with formulae which are placed into Column F. If you have any other column which will be filled by hand or with solid data (meaning no formula) you may rely on that column instead of F to find the upper end of the range in which to work.

Ciao,
Holger

[joh46k]

@HaHoBe :
I have few questions
1) If i want to add more column B and F to k . what i need to do. in the above code , just change the "F" to G ,h ..... ?

2)Why in the code don't have the sheet name like (Example : Sheet 1 )

3) If i want to put into user form code, not working but its is working in module and sheet.

[HaHoBe]

Hi, joh46k,

ad 1:
either do as you suggested or have the column being passed from another macro like

Please Login or Register  to view this content.

Instead of copying the macro and changing the column letters for each you would need to add the column letters to the array in CallMacro and that will get the macro going for all columns you want to run it in.

ad 2:
The code will be executed on the active sheet. If you want it to be executed on a different sheet than that you would need to make sure that you either activate that prior to the macro (maybe add a code line to CallMacro). If you want the macro on different sheets you may add another parameter to pass to the macro (which would be needed to be adjusted accordingly holding both the parameter and rely on that in the code which would need adjustment as well).

ad 3:
you would need to explain from which control this should be done. You may call the macro from most any control and keep it in a standard module. Example:

Please Login or Register  to view this content.

Ciao,
Holger

[joh46k]

Hi Hahobe , thank you so much for the reply .very kind of you

code #14 . working in sheet very well but in userform1, it showing "Run time error '13' type mismatch " . i click debug they highlighting on below line

Please Login or Register  to view this content.

why the above error occurs . is it because one of my column have some problem is it ?

[HaHoBe]

Hi, joh46k,

sorry - right now I tested it the code with an UserForm on a small sample and could not throw an exception. Will think about it and come back if I have found any possible reason.

Ciao,
Holger

[joh46k]

ok sure !. i attached file below so it will be more clear to you.


Why i need to delete blank cells in ALL sheet because ,if you view my sheet 1 code Private Sub Worksheet_Change(ByVal Target As Range). that code is to draw a Automatic border.

It will works only if I delete my blank cells , if not my sheet will have long vertical lines(look at my Orginal.jpg in attachment)

That vertical lines , happens because i have formula in blank cells , from Column B and F to k.

[HaHoBe]

Hi, joh46k,

in the last code I posted the events wonīt get triggered. Once this code is run you would need to alter any cell inside the last line of the data area to trigger the event again (this could be done at the end of the macro as well).

I donīt get the point right now as both codes are executing like they are supposed to do. If you change the look of a sheet by adding formulas you canīt blame the code for doing what it is to be supposed to do while you expect it not to act on that.

Ciao,
Holger