Free Microsoft Excel 2013 Quick Reference

Sorting a worksheet with blank space values

I was wondering if someone could help me with an issue. My problem somes when I try to sort my spreadsheet by a row. My spreadsheet is over 300 pages long. The way it is set up is when a user enters a value in one column an answer is generated in another column. If not it is just a blank space. Here is the formula in excel:
=IF(H4="","",IF(F4>H4,F4-H4, H4-F4))
Now my question is this. I thought by default when you sort in ascending or descending blank cells always go to the bottom. When I sort descending all the blank values come first and then the information I really want is at the bottom. I was wondering is there a way so that the blank cells will always be sorted to the bottom? I hope that you guys understand what I am trying to say. Any help would be greatly appreciated.

Post your answer or comment

comments powered by Disqus
I have a worksheet with 10,000 rows and 10 columns. In one of those columns
about 25% of the cells are empty. When I export the worksheet in a tab
delimited file those blank cells are simply omitted, so in those cases the
output record only has nine fields.

I need either to put something (the data in the column is numeric but always
>=0) to indicate a blank (say -1) or to find some way to make the data export
put in two consecutive tabs with a blank between them representing the blank

Either solution would be OK, but I sort of prefer filling in the fields.
Thanks for your help - jjk98

I've created a worksheet with a drop list (i used data validation) but I need
to sort the names by alpha, when I do this the corresponding drop lists don't
"follow" I don't think I have any hidden columns or rows. Any ideas?

Hi all,

This is my first post in this forum and first of all i want to thank you all in advance for your priceless help.

My problem is this:

I have a worksheet where the user input data and i have to make a macro that exports certain cell from the worksheet to a text file without any separating symbol, just like “CONCATENATE and EXPORT”. This is not hard to do, i’ve done it already. But the difficult part is here. My customer wants this macro to be interactive with the user and with an AS400 server that takes the textfile and reads it to find the value of certain variables, something like the opposite process of CONCATENATE. The hard part for me is that if any of the cells concatenated is blank i have to fill the text file with blank spaces.


If the concatenated cells are:
|one| two| three| tour| five|

the exported text to the text file will be:


But if one of the cells is blank:
|one| |three|tour|five|

Then the output have to be:

one threefourfive

The hole in the line have to be as long as "two" (3 chars)

May be is an easy thing, but have been days wondering, but nothing went out of my brain.

Can anybody help me with this? I’ll be very glad, and my customer will be too.

Again…thanks you in advance.


I have an ActiveX Listbox and combobox on a worksheet. The combobox has values which represent the headers of the listbox. When the combobox changes, a database (on a seperate sheet) sorts itself according to the combobox value and the listbox should then change. Here's my code:

    Sheets("pfactors").[c1] = Application.VLookup(cmbxSort.Value, Sheets("pfactors").[psorts], 2, False) 
    Selection.Sort Key1:=Range(Sheets("pfactors").Range("c1").Text), Order1:=xlAscending, Key2:=Range("C1") _ 
    , Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:= _ 
    False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _ 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
pFactors contains the values for the combobox rowsource. C1 (the result of the lookup) will contain a cell value (A1, B1, etc) which determines what column to sort by. AllParts is the dynamic range name of the database, which is stored on Sheet2.

Whenever I run this code, I get an error in the sorting section (also, if I don't use Sheets("sheet2").select, excel can't seem to find the range.

Now, I created a userform with a listbox and a combobox, doing the exact same thing, using the exact same code with the exception of the combox name. Everything else is 100% exact and it works like the gem.

What is up with the worksheet version that I'm missing?


Is there a way to sort a column with a series of formulas? ie select the
range and return the lowest value in row 1, next value in row 2 etc. The
range may contain blanks, which should sort to the bottom.
Thanks in advance!

I don't know how to populate a worksheet with the values selected from a userform listbox that has been populated dynamically from a named range.

I can create a copy of the Master worksheet and name it based on the dropdown rooms, and I have got the listboxes to generate a

checkbox list correctly, but am now unsure as how to get this information back onto the newly created sheet, as I am unsure what name each checkbox will have.

Also can you pick up the text label of a checkbox which is set to true to use that as well.

Any help would be appreciated. I have tried to upload my spreadsheet but the attachments manager says I don't have permission

I am looking for code which will protect a worksheet based on the value of a particular cell.

i.e. If cell A1=1, then the worksheet will be protected (ideally with a pswd)

Suggestions please?

I have a problem trying to set the references to sum/subtotal a colum with blanks. I have the macro written to find the next blank cell starting at the bottom of my data and working up but I can't get the formula right to sum the numbers above. Here is what my data looks like

Supplier Debit Credit
Vendor A $60 $25
Vendor A $75 $15
Vendor A $55 $35
Sum Blank Blank
Vendor B $60 $40
Vendor B $125 $25
Sum Blank Blank
Vendor C $65 $14

So I want the blanks to be the sum of the numbers above them. I am working from the bottom up so I don't know how to reference the cells to add when they vary (i.e. not always the same number of rows for each vendor. here is my code so far
Sub Macro6()
' Macro6 Macro
' Macro recorded 2/27/2007 by User

Dim ws As Worksheet
Dim myCol As Long, lastRow As Long
Dim i As Long, firstRow As Long
Dim strHeader As String

Set ws = ActiveSheet

myCol = 3
firstRow = 2
'find last Row in myCol
lastRow = ws.Cells(ws.Rows.Count, 3).End(xlUp).Row

For i = lastRow To firstRow Step -1
If Cells(i, myCol + 2) = "" Then
Cells(i, myCol + 2).Select
ActiveCell.FormulaR1C1 = "=SUM($R$C:$R$C)"
End If
Next i

End Sub

Any help would be greatly appreciated

I'm trying to make a function that shows the number of A*+'s in a list of grades and ommits all blank entries. What is wrong with this formula =COUNTIF(G2:G222,"A*+"))
That is what I am currently using ns it gives me too large numbers (it counted 285 in a column with 220 spaces.) There are several more grades that I need to count also and I tried using an accumalative one ie: =COUNTIF(G2:G222,"A*+)+COUNTIF(G2:G222,"A*).... etc...

When I use these formulas it comes up with the error: "Formula refers to empty cells" as some students did not receive a grade in that subject. Is there any way that I could ommit these blank entries from my formula without going through and de-selecting them all, (there are over 200 students.

Thank you and any help is appreciated.

Hi - I'm having problems populating a cell with the current value of a
variable. Basically, I'm trying to do the following:

- set the variable to the value within a specific cell
- paste over a section of a the sheet which inlcudes the cell which was used
to set the variable value in the steop above with blank/null cells
- populate the original cell used to set the variable value with the
variable value

Below is the code I have written which is not working:

Sub clearcontent()

Dim testvalue

Sheets("Frequency Input").Select
Set testvalue = Selection

Sheets("Header").Visible = True
Sheets("Frequency Input").Select

Selection = testvalue

Application.CutCopyMode = False
ActiveWindow.SelectedSheets.Visible = False

End Sub

I have a data matrix that helps me sort out information that is output by my company's reporting program. It details each worker's hours and whether they were on break or working and for how long. There are circumstances under which gaps may occur and that is what I'm trying to capture. In the example you will see that my matrix contains a great deal of spaces. What I'm trying to achieve is a summary of this matrix data that consolidates all of the information, often only 4 or 5 cells of data from 2000+ rows, onto a separate page. Please bear in mind that I know nothing of VBA or any other programming language outside of Excel's built in capabilities.

Example Data.xlsx

I have two files (Excel 2007 format). In file one, I have a worksheet with graphs.
I want to transfer that worksheet to another Excel file as a new worksheet in file #2.
When I simply select all for the worksheet in file #1 and then copy and paste it into a blank worksheet in file #2, all the references for the graphs on the pasted sheet refer back to the data on the worksheet in file #1. I want the graphs on the worksheet in file #2 to refer only to the data on it's own page.
Where did I mess this up???

I am not an experienced user of Excel 2003 and am having a few issues with Lists.
Basically, I have a worksheet with a list defined, and I can dynamically add a row to the list as required until I turn on Worksheet protection when all I am able to do is view the List.

I have tried enabling the options under worksheet protection to no avail.
The list is not static, i.e. there are new entries on a daily basis.
There is a summary sheet which calculates against the list entries - formulas being dynamically updated with every new row entry within the list.

I need to protect the worksheet as there are some calculation fields within the list and I also will then need to "split access" to the list by means of using the "add users to edit ranges" function.

Any help will be gratefully received

i'm trying to copy a worksheet and paste special values (to break all the links)...

the following code looks "good", but errors on the paste:=xlpastvalues...

ws.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
:=False, Transpose:=False 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

thank you...

Can I create a worksheet with fixed numbers of row and column?

e.g. the worksheet only has 40 columns and 20 rows


I need a macro to select a cell (in A collumn) with the same value as other (in B502)

Example: In B502 I have a vlookup formula that gives me "names".

I need a macro that select in the sheet, in the collumn A the cell that contents the same name.

Thank You!!!!!!!!

I have a worksheet with multiple columns and rows that contain sensitive
data. I need this data to develop a price sheet but do not want everyone to
see this data. I use the filters to make my list more viewable and hide a
bunch of columns that are for my eyes only. The problem is anyone can still
unhide the columns after I protect the worksheet. I haven't been able to
figure out how to show just what I want and not have the other info
accessable. Appreciate your help.

I have a worksheet with named lists. I have a second worksheet that has
validation references (=listname) to those lists to create dropdown lists.
This is working fine. I have now distributed this spreadsheet to multiple

I want to now update one or more lists in my master file and re-distribute
the list worksheet and give the users a method to replace the lists worksheet
in their local copy.

What is happening is that when I copy the updated list to the local
worksheet, and delete the original list worksheet, the second worksheet no
longer sees the named lists. The same thing happens if I first delete the
list worksheet and then copy the new list worksheet. The lists are still
correctly visable in the lists worksheet.

How do I get the second worksheet to see the named lists again?

how do I sort a worksheet based on one column?

How di I input a - into a text field

I have a worksheet with some cells(3 colluns) that are data input an
other cells that are formulas (10 colluns).
I want create a macro that will execute all formulas.
The general idea is:

some one send me a spread sheet with all datas, let say, the 2 collun
with 10 lines filled up with data
I open it and just click the macro button and I'll have the 2 origina
colluns and other new 10 colluns with the information created by th

Some of the formulas colluns are intermediate steps to a final result
Let's say that I do a MID, but I need get the LENght of the word in
separate cell and then I use this cell on the MID cell.


Message posted from

I am trying to sort a column with numbers and text eg;
these are identification tag's for valves.
I have over 2700 items with differnent code numbers!
I need help !

Excel 2007 SP2 crashes when attempting to select multiple values in a pivot with lot of values

I have a problem with both Excel 2003 and Excel 2007.
We work with a pivot table sourced from a Analysis Services cube.
When I put the dimension attribute in the row area and try to select multiple values I get a bug.
In Excel 2007, the bug will happen only if the list has many values (17000 in this case)
If I select 2 or 3 or more values in the list, Excel 2007 SP2 crashes immediately.

With Excel 2003 SP3, the bug is different, it simply ignore the multiple select and always shows everything. Furthermore, there is a private hotfix kb947648-fix that repairs this bug.

Is there any hotfix to repair the Excel2007 version of this bug?
PhilippeThanks/Regards,Philippe Cand

I know there is a way to imprint a worksheet with the entire path (e.g. X:susand/etc/etc) by entering a formula.
I used to use this function and forgot the formula.

I remember that it begins with =filename( )
But, I do not remember what goes inside the parentheses.


Excel 2003. I am trying to programmatically sort a worksheet. I used the
Macro recorder to do it and copied the code. My workbook is open and the
worsheet is open (ShNew). Yet, the following gives me an error:

ShNew.Range("A2:F" + Trim(Str(I))).Select
Selection.Sort Key1:=Range("D1"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

What am I doing wrong? Or better yet, simply, "How do I perform the sort on

Dr. Doug Pruiett
Good News Jail & Prison Ministry

No luck finding an answer? You could always try Google.