Using A Macro To Sort Based Upon A Cell Value

I am having trouble with a sort. I would like a list sorted descending, so that the highest dollar amount is at the top. However, if there are blanks or zeros in the list, it is putting those first. How do I get it to ignore them or put them at the bottom?

Thank you,
Liz

Hi everyone,

I found an excellent macro this morning that allows the user to filter a pivot table based on the value found in a specific cell. This cell essentially acts as a search bar, allowing the user to type in what they are looking for rather than select it from a drop-down list.

The macro works perfectly for my purposes except in one regard: I can no longer perform a "show all" filter. If I leave the "search bar" cell blank, the pivot table shows nothing. I'm sure that there is an easy fix for this but I'm still learning the basics of how to write and use macros. How can I change the code so that when I leave the cell blank, the pivot table shows all? The search bar cell is D2.

Thanks for your time!

Hi,

Can someone please help? I've got quite a lot of data that I want to sort
by the persons date of birth, but because I have cells with formula in it
(this works out the persons age) the sort function is changing the formulas
so the formulas no longer work becuase it changes the cell where it is
getting the data from. Does anyone have any ideas how this could be fixed??

Thanks!

Hey guys,

I'm trying to write a macro which prints to PDF and saves the file name as the contents of a cell. I've been looking through all the posts currently on this forum to get something working. I'm using the following code -

Sub PrintPDF()
Filename = "C:\Documents and Settings\samb\My Documents\" & ActiveSheet.Range("Z1").Value
SendKeys Filename & "{ENTER}", False
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Adobe PDF:", Collate:=True
End Sub

The macro prints to PDF, but then it stops at the Save As stage, where I have to manually enter the name of the file and click Save. I want the macro to automatically name the file with the contents of cell Z1. I then want it to then automatically press enter. Any ideas where I'm going wrong? Any help would be much appreciated!

I am looking for the code to select the visible data after applying a data filter. Actually I know how to select the data after applying the data filter but the issue is I am not able to exclude the header row and give the target range as used (non-blank) rows only!!

I am using below code to Select the Visible rows in the target range:

Code:

Range("A:p").SpecialCells(xlCellTypeVisible).Select

Problems in this code a

1) after applying the filter, while selecting the data it is selecting all the rows in given range till last row on the workbook. I need this to select the the data only till the last used row in the given range.

2) It is not possible to provide the address of the first row after we apply the filter since the first row address may change depending on the values in the table.

E.g. 1st time when I am running the macro the first row in the visible filtered data is starting at Cell address A4 and next time when I will run the macro it may be A6

3) The Code is also selecting the 1st row which is a header row. How can we exclude it from selection.

Some one please revert with the solution.
Thanks in advance.

I have an Excel workbook that was created by a former coworker. It includes a macro that, among other things, displays a message box about the 2008 file. The macro runs as soon as the file is opened. I'd like to access that macro to correct the date to 2011 and see what else, if anything, it's doing for me (it doesn't appear to do much). I can find references to creating macros to hide and unhide rows/columns and I found ways to delete all macros in a workbook, but I cannot find anything about unhiding a macro without knowing its name.

Does anyone know of a way to unhide this macro?

Thanks!

Hi all.

I have set up a workbook that is sent out to lots of different users. They each keep and use their own copy.

I have set it up so that everything looks OK and is visible on MY screen, but I'm conscious that some users may have different screen sizes, different toolbars set up, and so on, which might make some parts not immediately visible to them.

I have set up an auto-execute macro which automatically sets the zoom factor to best fit, for several of the worksheets, and this works fine.
Here's the code that does it.
Code:

Sheets("WELCOME").Select
    Range("A1:N18").Select
    ActiveWindow.Zoom = True

By repeating this code for each worksheet, I can make each one be zoomed just right.

However, the file contains 8 sheets that are all identically laid out, except the number of rows is different.
What I want to do is go to the worksheet that has the largest number of rows (it's always the same worksheet, so I know which one it is), set the zoom factor for THAT worksheet (which I can do, and it always has the same number of rows), and then take THAT zoom factor, whatever it is - and it will vary depending on the user - and apply that to the other worksheets that have a similar layout.

I could just go through each worksheet and zoom it automatically, but that would mean that some of the sheets looked very large, others very small, and I'd like them to have a consistent appearance.
I could also specify a range on each worksheet that was similar to the appropriate range on the longest worksheet, and zoom that automatically, but that's not ideal either, because some of the row heights vary from sheet to sheet, and again I'll end up with different font sizes.

Anyone know how to do this ?

Good Morning Folk

Is there a way to sort a list of names alphabetically and put any blank cells to the bottom rather than the top

Cheers Jim

Hey there,

I have been tasked with introducing userforms into an excel sheet and tbh I'm quite amazed that excel has this capability of adding userforms to excel sheets.

Anyway, I have 2 columns of data in an excel sheet and I wish to add this to a userform so that the userform displays the 2 columns beside each other with headings, like a table. The user should then be able to select a particular row and insert it into the specified cell.

I would also like the user to select a row on the table and then be able to bring up another table depending on the row selected...basically so that the user can draw deeper into the information that they require.

I have an example excel sheet where I have 2 sheets. One sheet is the user entry sheet called User Entry Screen. the next sheet is the tables sheet where my tables are held. Once the user selects the cell shown in the example sheet, it should then bring up the user form. the user then, depending on which item clicked, then gets shown the next window with a table and info on it. then user should then be able to select an item and the cells on the user entry screen would then get populated.

Personally I think this is a really tricky challenge and any help with doing this would be extremely appreciated.

I'll post up further comments as I am trying to work my way through it!

Thanks,

Jag

Arcangelo from Italy asks: How can I write an Excel VBA macro to save the current Excel file with a filename derived from cell A1?
This macro is amazingly straight-forward:

Public Sub SaveAsA1()
ThisFile = Range("A1").Value
ActiveWorkbook.SaveAs Filename:=ThisFile
End Sub
I'm totally lost on this "amazingly straight-forward" macro!!!! Could someone help if I tell you the SAVE location? It's T:\COMMISSIONING\IJT\TIMELOG project\Staff#1. I'm just not certain what value I'm replacing in the Macro above.

Any help appreciated..

Hi Excel experts,
I am working on a worksheet, and would like to have an entire row transferred to another worksheet based upon a certain cell's value.

Having searched through the many solved threads on this site, I attempted to customize some of the code to my worksheet. Not being proficient in Macro/VBA, I failed. I figured I would ask for help, and see if anyone can provide what I am looking for.

Basically, I am attempting to construct a "work in process" file, and would like all completed work to be deleted from the work in process sheet, and transferred to a sheet that contains a running list of all work completed.

Attached is an example of what I am working on. I would like Column Q (Status) to determine if a row can be moved to a sheet that contains the list of all work completed, and eliminated from the original sheet. Once Status is updated to "Complete", it would be helpful to be able to run a macro/click a button that would perform the transfer.

Can anyone assist?

Thanks!

Hi All,

I have the below code which deletes all items from a listbox and my excel sheet which is the source for populating that listbox. I am using a option button style for my listbox and the selection style as single .i.e. you can select only one item at one go in the lisbox. I want my macro to delete the selected item from my worksheet .i.e. it's entire row so that it doesn't reflect in my lisbox any more. Below is my code :

Code:

Private Sub CommandButton2_Click()
 'REMOVE SELECTION
 
  Dim I As Long
 
    With ListBox1
      For I = .ListCount - 1 To 0 Step -1
       If .Selected(I) Then
           .RemoveItem I
           Sheets("URL List").Rows(I + 2).EntireRow.Delete
           
        End If
      Next I
    
End With

End Sub

Thanks a lot for your help in advance.

Hi there, longtime user firsttime poster. Looking for some help as I am a non-expert with macros. Here's what I'm trying to do:

We have to submit things to a certain regulatory body and we usually enter tasks in as soon as they come, do the submission, and then keep a record of that submission.

So, I have a workbook with two sheets, one is "TO DO", the other is "ARCHIVE". Both sheets have the same columns and everything. I am looking for a macro that will automatically cut a (row) from the TO DO sheet and paste it in into the ARCHIVE sheet once it is done, then delete the cut row from the TO DO list so it stays topped up.

The trigger for archiving is the columns M and N which are titled "Complete ?" and each has a validation drop down that says "YES". When both cells in columns M and N have the YES in them, I would like the macro to make the above mentioned actions.

I ran a search on the forums and found something similar, but not quite what I was looking for.

Any help?

Good afternoon!

I'm working with Excel 2003 sp 3 on Windows xp.

I have been banging my head trying to jar loose the formula to calculate man days where 1 equals 8 hours. I have one that was given to me by a coworker:

=INT(Q3/8)+(((Q3/8)-(INT(Q3/8)))*0.8)

That calculates man days from total hours. I need a formula that will total man days and return the man day answer. Example:

weekending 12/25 = 16.2 which is 16 man days and 2 hours
weekending 1/1 = 24.7 which is 24 man days and 7 hours

Total = 41 man days and 1 hour

I have also been asked if there was a way for a number to be entered into a spreadsheet and automatically put in order without a macro or "pushing a button".

Any advice is greatly appreciated!

Happy New Year!

~ Brenda ~

I have sheet with rows and each row has formulas based on cells in that row.
I would like to have a new row automatically inserted with the formulas
updating to reference the cells in the new row. Let's say I start with 50
rows and after they are filled up, a new row is added automatically? How do I
do this?