trying to run a sort to highlight rows and clear the cells in those rows....

Hello Excel experts, here is my need.

I have set up a spread sheet with validation lists in one cell on each row. The user can choose "funded", "in process" or "cancelled". At the end of the month, I want the user to be able to push a data button with a macro assigned to it. It is the macro that is giving me fits. Here is what I want the macro to do:

Prompt user to save worksheet and pause to allow the user to enter the name of the saved file. Then, after the save is complete, the macro would clear the contents of all rows that had the "funded" category in it. I don't want to use delete as I want the formatting to remain in all 110 rows of this sheet. After clearing the indicated rows, the remaining rows would collapse so there are no blank rows between filled rows.

Can excel do this? Thank you to the one(s) that take on answering this.

James

Hi James,

This code will do it for you :

Option Explicit

Sub Rename_File_And_Save()

Dim OldFullName As String, NewFullName As String

'.....get the full name of the existing workbook

OldFullName = ActiveWorkbook.Path & Application.PathSeparator & _
ActiveWorkbook.Name


'.....skip deletion of the old file if user cancels the "Save As"
' process, or doesn't change the name or path before
' pressing OK, or if "Save As" terminates abnormally.

On Error GoTo SkipKill

'.....show the "Save As" dialog box
' (give user opportunity to enter a new name and path)

Application.Dialogs(xlDialogSaveAs).Show

'.....active workbook now has a new name, so delete the old
' workbook (which is no longer loaded into Excel).

' Kill (OldFullName) ' the Kill statement will exit with an error if the
file to be killed is still active

Clear_Hide_Rows

SkipKill:
End Sub

Sub Clear_Hide_Rows()
Dim MyCell As Range

Sheets(1).Range("1:30").Validation.Delete

For Each MyCell In Sheets(1).Range("A1:A30")
If MyCell.Value = "Funded" Then MyCell.EntireRow.ClearContents
Next

For Each MyCell In Sheets(1).Range("A1:A30")
If IsEmpty(MyCell) Then MyCell.EntireRow.Hidden = True
Next

End Sub

Sub Rows_Unhide()

Sheets(1).Range("1:30").EntireRow.Hidden = False

End Sub


With respect to code above, please note that :
-You need to adjust sheet references, row numbers etc to suit your needs
-No hidden rows or columns should be there on your worksheet or the code
will not work
-Validation has to be deleted in order to be able to hide rows
-If you need to unhide rows later, use the sub called Rows_Unhide

Let us know if it worked !

Amit
"James" wrote in message
...
Hello Excel experts, here is my need.

I have set up a spread sheet with validation lists in one cell on each
row. The user can choose "funded", "in process" or "cancelled". At the end
of the month, I want the user to be able to push a data button with a macro
assigned to it. It is the macro that is giving me fits. Here is what I want
the macro to do:

Prompt user to save worksheet and pause to allow the user to enter the
name of the saved file. Then, after the save is complete, the macro would
clear the contents of all rows that had the "funded" category in it. I
don't want to use delete as I want the formatting to remain in all 110 rows
of this sheet. After clearing the indicated rows, the remaining rows would
collapse so there are no blank rows between filled rows.

Can excel do this? Thank you to the one(s) that take on answering this.

James

Wow, that was a mouthful to read! I was extremely
busy yesterday. Sorry I did not respond sooner. Thank you
for yourhelp! I will try this today and get back to you
with the results.
-----Original Message-----
Hi James,

This code will do it for you :

Option Explicit

Sub Rename_File_And_Save()

Dim OldFullName As String, NewFullName As String

'.....get the full name of the existing workbook

OldFullName = ActiveWorkbook.Path &
Application.PathSeparator & _
ActiveWorkbook.Name


'.....skip deletion of the old file if user cancels
the "Save As"
' process, or doesn't change the name or path before
' pressing OK, or if "Save As" terminates abnormally.

On Error GoTo SkipKill

'.....show the "Save As" dialog box
' (give user opportunity to enter a new name and
path)

Application.Dialogs(xlDialogSaveAs).Show

'.....active workbook now has a new name, so delete the
old
' workbook (which is no longer loaded into Excel).

' Kill (OldFullName) ' the Kill statement will exit
with an error if the
file to be killed is still active

Clear_Hide_Rows

SkipKill:
End Sub

Sub Clear_Hide_Rows()
Dim MyCell As Range

Sheets(1).Range("1:30").Validation.Delete

For Each MyCell In Sheets(1).Range("A1:A30")
If MyCell.Value = "Funded" Then
MyCell.EntireRow.ClearContents
Next

For Each MyCell In Sheets(1).Range("A1:A30")
If IsEmpty(MyCell) Then MyCell.EntireRow.Hidden = True
Next

End Sub

Sub Rows_Unhide()

Sheets(1).Range("1:30").EntireRow.Hidden = False

End Sub


With respect to code above, please note that :
-You need to adjust sheet references, row numbers etc to
suit your needs
-No hidden rows or columns should be there on your
worksheet or the code
will not work
-Validation has to be deleted in order to be able to
hide rows
-If you need to unhide rows later, use the sub called
Rows_Unhide

Let us know if it worked !

Amit
"James" wrote in
message
news:5CF0A02E-976F-4AB9-B73C-
...
Hello Excel experts, here is my need.

I have set up a spread sheet with validation lists in
one cell on each
row. The user can choose "funded", "in process"
or "cancelled". At the end
of the month, I want the user to be able to push a data
button with a macro
assigned to it. It is the macro that is giving me fits.
Here is what I want
the macro to do:

Prompt user to save worksheet and pause to allow the
user to enter the
name of the saved file. Then, after the save is
complete, the macro would
clear the contents of all rows that had the "funded"
category in it. I
don't want to use delete as I want the formatting to
remain in all 110 rows
of this sheet. After clearing the indicated rows, the
remaining rows would
collapse so there are no blank rows between filled rows.

Can excel do this? Thank you to the one(s) that take
on answering this.

James


.