PDA

View Full Version : Solved: Find or Match - to delete column



debauch
12-03-2006, 07:18 AM
Hello,

I am working on some code that will remove private information from our work files to adhere to a privacy act. I have been trying to streamline the process and need a little help.

There is about 20 files, where I am using a open file dialog , then the code finds certain columns, deletes the private info, takes the first tab only, and saves it to one of our public drives for viewing.

Right now in my example, it only deletes what I need, specified in column H:H , however the same column(s) will appears in different spots for each file.

Here is what I have so far:

Selection.AutoFilter 'take off filter
Cells.Find(What:="SSN", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Columns("H:H").Select
'ActiveSheet.Cells(Rows.Count, 1).End(xlUp).EntireRow.Copy << tried _
this but it didnt work

Selection.Delete Shift:=xlToLeft


Sheets("Users").Select 'copy whole thing
Sheets("Users").Copy ' make new seperate front page
Range("A2").Select

ChDir "L:\Hal Reports"
ActiveWorkbook.SaveAs Filename:="DB 1" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

' Windows("DB 1.xls").Activate


I think something similar to below might work? I dont want it to look to H:H but rather, look to the column named SSN in each book.

ActiveSheet.Cells(Rows.Count, 1).End(xlUp).EntireRow.Copy


Thanks.

mdmackillop
12-03-2006, 10:29 AM
OK
You're finding a value, but not using it as the basis for your selection. Also, it's not neccessary to Select before deleting. Similarly the sheet can be copied without selecting.
Try

Selection.AutoFilter 'take off filter
With ActiveSheet
.Cells.Find(What:="SSN", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).EntireColumn.Delete
.Copy
End With
ChDir "L:\Hal Reports"
ActiveWorkbook.SaveAs Filename:="DB 1", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

debauch
12-03-2006, 11:30 AM
So far so good.

One more question. How can I have the workbook save as the existing workbook name? Right now, based on the above code, all files will save to the same name.

ActiveWorkbook.SaveAs Filename:="filename"

mdmackillop
12-03-2006, 11:37 AM
ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Name, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

debauch
12-03-2006, 02:06 PM
Thank you.