YTLE#87: Remove Rows w Blanks Go To Trick

Hi

I'll probably get knocked over in the rush here!

I saw a spreadsheet this morning that had hidden rows & cloumns, quite normal really, but this sheet had expand & compress '+' & '-' buttons outside the cell reference bars, much like you see in Windows Explorer, these enabled you to quickly hide/unhide what I'm assuming are pre-determined sections of the sheet.

How is this done?

Rossco

"Excelover Exceloser"

Am i able to run a macro or something so that if a cell is blank it removed the entire row.

Example: Below the table is from A1 to D23 and i need to automatically remove row 3, 6, 8 etc as column A is the main column

JobDate
WorksOrder Quantity Cost 40292 251730 1064 111.72 251730 40292 251730 NF06 1360 40292 251730 0.07 NF06 251730 1360 95.2 40292 251670 2544 407.04 251670 852 59.64 40292 251670 NF06 852 40292 251670 0.07 NF06 40292 251670 852 59.64 40292 251669 4381 569.53 40292 251669 3328 0 40292 251669 3328 40292 251669 0 NF03 40292 251669 3328 0 40293 251840 670 288.1 40293 251840 880 61.6 40293 251840 NF06 880 40293 251840 0.07 NF06 40293 251840 880 61.6 40293 251837 2694 350.22 40293 251837 900 63

I have posted questions concerning this problem on other forums. I have macro that does some calculations. It works like this.

1) The user is to paste info (from another program viz. SimCorp Dimension) onto a spreadsheet and then press a button.
2) when pressing the button my macro is started. The first part of the macro is to search the info on the spreadsheet for any uneccessary blanks. Sometime the values that the user pastes into the spreadsheet are written with thousand separators e.g. "4 000 000". Since I want to use the values for computations I must remove these blanks.
3) Calculations are made.

The problem is that I do find and remove the blanks but in doing so the format or some other type of setting is changed for the cells that have had blanks (that are now removed). The cells looks fine and you can add the in the spreadsheet one by one. However if you try to use the sum function or I try to add them together in the macro it does not work. The code for finding and removing blanks ( I got this from Norman Jones who has helped me alot) is:

Public Sub findAndRemoveBlanks()
ActiveSheet.UsedRange.Replace What:=Chr(160), _
Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
End Sub

If you have any idea on how to solve this I would be most greatful for your help. Also if anyone should be willing to look at my spreadsheet (with the values inside it) I think that would be very good since I really have tried to solve and I have gotten alot of help on various discussion forums but I still cant solve it. Thank you all very much!!!

Hi all,

I am looking for Vba code.

I have 2007 excel file .

in This File A to Z column Used.

If column A is blank means remove all the A to Z data for the Particular Cell.

For Example

A4 is blank so i want to remove a4 to Z4.

A25000 is blank means i want to remove A25000 to Z25000

hi i need a macro function to remove blank values from a range "ad1:ad100"

in the column i have a db recordset output that varies but there will always be a blank somewhere in it i have a combo box referenceing this range and do not want a blank enter in the list

e.g.

a
b
d

e
f
h

should show

a
b
d
e
f
h


thanks!

=IF(ROW()-ROW($A$1:A10000)+1>ROWS(Macros!$A$1:A10000)-COUNTBLANK(Macros!$A$1:A10000),"",INDIRECT(ADDRESS(SMALL((IF(Macros!$A$1:A10000"",ROW(Macros!$A$1:A10000),ROW()+ROWS(Macros!$A$1:A10000))),ROW()-ROW()-ROW($A$1:A10000)+1),COLUMN(Macros!$A$1:A10000),4)))

This formula is returning #NUM....can someone help out with what the error is?

Hi all,

How is this done?........

Check through column B and if a cell is found to be blank, clear the corresponding data in column C, loop until all cells in Column B have been checked.

Appreciate any help and thanks in advance

Data is in a horizantal list, say row 2:

A2: SAM
B2:
C2: MARY
D2: JOE
E2:
F2: DAViD

i.e. like this:

SAM-blank-MARY-JOE-blank-DAVID

I am looking for formulas (not VBA) to compact this list by removing the blanks so the new will be placed in K2, L2, M2 etc. as follows:

SAM-MARY-JOE-DAVID

This is a good article but only works on vertical lists:
http://www.cpearson.com/excel/noblanks.htm

Thanks.

I have a macro that does alot of things. It is used for a spreadsheet in
which the user can paste in info from other programs. In order for this to
work my macro must search all used cells, check if their content is purely
numeric i.e. "15545" and "4457676,15" and "546 222 111,333 114" are purely
numeric whereas "AA1" or "Delta 1" is not pure numeric. If the contents of a
cell is purely numeric then the macro shall search the contents inorder to
find blanks and re move them. The reason for this is that Excel cannot add
e.g. "555 111 222" and "500" since the first number is maltreated due to the
blanks. I have written several subs that supposedly did this quuite simple
task and I have also tried recording a macro but they always seem to be
somewhat wrong. I submit code as examples of what I ahve done and if someone
has a piece of code that they know work please post it. I sometimes want to
search an entire workbook and sometimes just a worksheet. Code:

Private Sub findAndRemoveBlanks()

Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

End Sub


Another example:

Public Sub findAndRemoveBlanks(s As String)
Dim WB As Workbook
Dim SH As Worksheet
Dim rng, rCell As Range
Set WB = ActiveWorkbook
Set SH = WB.Sheets(s)
Set rng = SH.UsedRange

For Each rCell In rng.Cells
With rCell
If Not IsEmpty(.Value) Then
If Not UCase(.Value) Like "*[A-Z]*" Then
..Replace What:=" ", Replacement:=""
End If
End If
End With
Next rCell
End Sub

Hi All,

The below code

Code:

Sub delblanks()
Dim MyRange As Range
Dim c As Range
Set MyRange = Range("A1:B14")
Application.Calculation = xlCalculationManual
    For Each c In MyRange
        If c = "" Then c.Delete xlShiftUp
    Next c
Application.Calculation = xlCalculationAutomatic
End Sub

helps remove blanks from a range of cells but there are two problems with it:

1- If I put in data like this over range A1:B14:

A B
1 Text Text


9 Text Text



14 Text Text

And when I run the code it changes above to:

A B
1 Text Text

5 Text Text

8 Text Text

Then on the second run:

A B
1 Text Text

3 Text Text

5 Text Text

And then when I run the code for a third time then it finally changes it to:

A B
1 Text Text
2 Text Text
3 Text Text

Also is it possible to alter the code so that it creates the final non-blank result in a separate worksheet rather than change the worksheet which has the formulas?

This is a cross-post from Mr Excel and as I have not recieved a response in the last 5 days(I also replied again in between) I was hoping someone could help here. The link to the cross post is:

http://www.mrexcel.com/forum/showthread.php?t=525646

Thanks