MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Can anyone help me with this????

Posted by Jack on November 24, 2001 11:07 AM

I have a spreadsheet that I need to delete rows if a portion of whats in one column is not found in the cells in a column on a referenced spreadsheet.

Basically I have a 4-digit number that is embedded within text in one of the columns and I want to be able to delete that row if that 4 digit number does not match any of the 4 digit numbers on a different spreadsheet.

Any suggestions?


Posted by Who Knows??? on November 25, 2001 9:16 PM

See if you can edit this to work for you...

Sub Convert_Macro()
' Written by Who Knows???

Dim File_Names As Variant
Dim File_count As Integer
Dim Active_File_Name As String
Dim Counter As Integer
Dim File_Save_Name As Variant

File_Names = Application.GetOpenFilename(, , , , True)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
File_count = UBound(File_Names)
Counter = 1
Do Until Counter > File_count
Active_File_Name = File_Names(Counter)
Workbooks.Open FileName:=Active_File_Name
Active_File_Name = ActiveWorkbook.Name
File_Save_Name = InStr(1, Active_File_Name, ".xls", 1) - 1
File_Save_Name = Mid(Active_File_Name, 1, File_Save_Name) & ".csv"
ActiveWorkbook.SaveAs FileName:=File_Save_Name, FileFormat:= _
Counter = Counter + 1
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub