Delete certain records in Excel and then Change a # in another col

J

jeannie v

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?
 
S

Stephane Quenson

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.
Click to expand...

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.
Click to expand...

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.
 
J

jeannie v

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.
 
J

jeannie v

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!
 
S

Stephane Quenson

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.
 
G

Gord Dibben

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
 
J

jeannie v

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,
 
S

Stephane Quenson

Gord Dibben said:
Stephane

You misunderstand the purpose of "ignore blanks".
Click to expand...

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.
 
Top