Hi Experts:

This is a long one and I hope someone will help...I don't think it's too
difficult, but I can't make it work.

I have a report that I'm building that has 15 .csv files that I consolidate
by copy/pasting each .csv file to another single worksheet to save as an .xls
file.

I am doing simple Macro Recording for most of the report formatting, etc.,
however, there is one point in the process that I need to delete all records
in Column A for about 5 or 6 Codes (i.e., Leadership, Training, etc. and
retain the rest.

I then want it to go to Column H and convert any number 3 or greater to 2,
EXCEPT, if Column G is "Doe". If it is "Doe", I want it to let the number
exist as it is logged.

Can anyone help me with this one?
--
jeannie v

I am doing simple Macro Recording for most of the report formatting, etc.,
however, there is one point in the process that I need to delete all records
in Column A for about 5 or 6 Codes (i.e., Leadership, Training, etc. and
retain the rest.

Sub DeleteSomeRows()
' This macro deletes rows containing certain values in Column A

For i = 1 To 65536 ' <-- Adapt to your needs!
If Cells(i, "A") = "Training" Or Cells(i, "A") = "Leadership" Or _
Cells(i, "A") = "Third Value" Or Cells(i, "A") = "Fourth value" Or _
Cells(i, "A") = "5th Value" Or Cells(i, "A") = "6th value" _
Then
Rows(i & ":" & i).Select
Selection.Delete Shift:=xlUp
i = i - 1 ' as we just deleted a row, we should not increase i
End If
Next i

End Sub



I then want it to go to Column H and convert any number 3 or greater to 2,
EXCEPT, if Column G is "Doe". If it is "Doe", I want it to let the number
exist as it is logged.

Sub UpdateColumnH()
' This macro caps the numbers in column H to 2 unless column G is "Doe"

For i = 1 To 65536 ' <-- Adapt to your needs!
If Cells(i, "G") < "Doe" Then
If Cells(i, "H") = 3 Then
Cells(i, "H") = 2
End If
End If
Next i

End Sub


Stephane.

Hi Stephane:

I'm going to run the report sometime tomorrow or Monday using my entire
Macro with your resolution to my problem. I will let you know how it
works.....Thank you for your help and expertise.
--
jeannie v


"Stephane Quenson" wrote:

I am doing simple Macro Recording for most of the report formatting, etc.,
however, there is one point in the process that I need to delete all records
in Column A for about 5 or 6 Codes (i.e., Leadership, Training, etc. and
retain the rest.

Sub DeleteSomeRows()
' This macro deletes rows containing certain values in Column A

For i = 1 To 65536 ' <-- Adapt to your needs!
If Cells(i, "A") = "Training" Or Cells(i, "A") = "Leadership" Or _
Cells(i, "A") = "Third Value" Or Cells(i, "A") = "Fourth value" Or _
Cells(i, "A") = "5th Value" Or Cells(i, "A") = "6th value" _
Then
Rows(i & ":" & i).Select
Selection.Delete Shift:=xlUp
i = i - 1 ' as we just deleted a row, we should not increase i
End If
Next i

End Sub



I then want it to go to Column H and convert any number 3 or greater to 2,
EXCEPT, if Column G is "Doe". If it is "Doe", I want it to let the number
exist as it is logged.

Sub UpdateColumnH()
' This macro caps the numbers in column H to 2 unless column G is "Doe"

For i = 1 To 65536 ' <-- Adapt to your needs!
If Cells(i, "G") < "Doe" Then
If Cells(i, "H") = 3 Then
Cells(i, "H") = 2
End If
End If
Next i

End Sub


Stephane.

Hi Stephane:

I've tried the Macros and it almost works as I want.....these are the
problems I'm having:

I need to keep the Header Row out of the Macro..it remove/changes the Header
when I run the Macros you gave me.

Next, When I run the Delete for the Dept Codes, there is a blank that shows
up as (Blanks) in the Dropdown that I also want to delete....how can I do
this?

Can youhelp me with this? Otherwise, it's working great!
--
jeannie v


"Stephane Quenson" wrote:

I am doing simple Macro Recording for most of the report formatting, etc.,
however, there is one point in the process that I need to delete all records
in Column A for about 5 or 6 Codes (i.e., Leadership, Training, etc. and
retain the rest.

Sub DeleteSomeRows()
' This macro deletes rows containing certain values in Column A

For i = 1 To 65536 ' <-- Adapt to your needs!
If Cells(i, "A") = "Training" Or Cells(i, "A") = "Leadership" Or _
Cells(i, "A") = "Third Value" Or Cells(i, "A") = "Fourth value" Or _
Cells(i, "A") = "5th Value" Or Cells(i, "A") = "6th value" _
Then
Rows(i & ":" & i).Select
Selection.Delete Shift:=xlUp
i = i - 1 ' as we just deleted a row, we should not increase i
End If
Next i

End Sub



I then want it to go to Column H and convert any number 3 or greater to 2,
EXCEPT, if Column G is "Doe". If it is "Doe", I want it to let the number
exist as it is logged.

Sub UpdateColumnH()
' This macro caps the numbers in column H to 2 unless column G is "Doe"

For i = 1 To 65536 ' <-- Adapt to your needs!
If Cells(i, "G") < "Doe" Then
If Cells(i, "H") = 3 Then
Cells(i, "H") = 2
End If
End If
Next i

End Sub


Stephane.

To not consider the header row, which I assume is row 1, change the line
For i = 1 To 65536
To
For i = 2 To 65536

To not show blank values in a validation dropdown box, you have to select
all the cells having validation and go to Data Validation, tab "Settings"
and make sure that the checkbox "Ignore Blank" is checked.

Hi Stephane:

Thank you.....it all works great!
--
jeannie v


"Stephane Quenson" wrote:

To not consider the header row, which I assume is row 1, change the line
For i = 1 To 65536
To
For i = 2 To 65536

To not show blank values in a validation dropdown box, you have to select
all the cells having validation and go to Data Validation, tab "Settings"
and make sure that the checkbox "Ignore Blank" is checked.

Stephane

You misunderstand the purpose of "ignore blanks".

Being unchecked/dissabled does not prevent blank from showing in the list.

From help.........................................

If the source list is a named range that contains blank cells, users may be able
to type any entry, without receiving an error message. To prevent this:

Select the cell that contains a data validation list
Choose Data|Validation
On the Settings tab, remove the check mark from the Ignore blank box.
Click OK
Note: If the source list contains blank cells, and is a range address, e.g.
$A$1:$A$10, it will block invalid entries with Ignore blank on or off.

Maybe check out Debra's site for creating dynamic named ranges so's blank cells
are not included.

http://www.contextures.on.ca/xlNames01.html#Dynamic

And this page may help.

http://www.contextures.on.ca/xlDataVal13.html


Gord Dibben MS Excel MVP

On Sat, 21 Jul 2007 12:16:00 -0700, Stephane Quenson
wrote:

To not show blank values in a validation dropdown box, you have to select
all the cells having validation and go to Data Validation, tab "Settings"
and make sure that the checkbox "Ignore Blank" is checked.

Hi Gord:

Thank you for your help..I appreciate your expertise. I'm really good with
the report so far...my next challenge is the pivot tables. Is there a way to
Create Pivot Tables in Macro? I have 3 Pivot Tables in this same
document....but I'm not sure if it's even possible to Macro the Pivot Tables.
If I can, I will be glad to post it to the Group Community.

Regards,
--
jeannie v


"Gord Dibben" wrote:

Stephane

You misunderstand the purpose of "ignore blanks".

Being unchecked/dissabled does not prevent blank from showing in the list.

From help.........................................

If the source list is a named range that contains blank cells, users may be able
to type any entry, without receiving an error message. To prevent this:

Select the cell that contains a data validation list
Choose Data|Validation
On the Settings tab, remove the check mark from the Ignore blank box.
Click OK
Note: If the source list contains blank cells, and is a range address, e.g.
$A$1:$A$10, it will block invalid entries with Ignore blank on or off.

Maybe check out Debra's site for creating dynamic named ranges so's blank cells
are not included.

http://www.contextures.on.ca/xlNames01.html#Dynamic

And this page may help.

http://www.contextures.on.ca/xlDataVal13.html


Gord Dibben MS Excel MVP

On Sat, 21 Jul 2007 12:16:00 -0700, Stephane Quenson
wrote:

To not show blank values in a validation dropdown box, you have to select
all the cells having validation and go to Data Validation, tab "Settings"
and make sure that the checkbox "Ignore Blank" is checked.

"Gord Dibben" wrote:

Stephane

You misunderstand the purpose of "ignore blanks".

Absolutely right! And I like the tip about OFFSET to increase the range
automatically. But it does not solve the case of blank cells in a middle of a
range, and in fact it is dangerous in such cases, as it drops the last
entries when you have blank cells. Therefore, it is a good practice to sort
ranges of cells that are used for validation purposes.