Macro to collapse/hide empty cells

[Qubit]

Hello everyone!
I am trying to create a macro that will be able to open a user selected file, search for a user input set of characters and when it finds the column that contains a cell matching those characters, hide all of the empty cells in that column. So far this is what I have:

Sub Get_Data()
FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a file to import", _
FileFilter:="Excel Files *.xls (*.xls),")
''
If FileToOpen = False Then
MsgBox "No file specified.", vbExclamation, "Duh!!!"
Exit Sub
Else
Workbooks.Open Filename:=FileToOpen
End If

Dim datatoFind
Dim sheetCount As Integer
Dim counter As Integer
Dim currentSheet As Integer

On Error Resume Next
currentSheet = ActiveSheet.Index
datatoFind = InputBox("Please enter the value to search for")
If datatoFind = "" Then Exit Sub
sheetCount = ActiveWorkbook.Sheets.Count
If IsError(CDbl(datatoFind)) = False Then datatoFind = CDbl(datatoFind)
For counter = 1 To sheetCount
Sheets(counter).Activate
Cells.Find(What:=datatoFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
If ActiveCell.Value = datatoFind Then Exit Sub
Next counter
If ActiveCell.Value <> datatoFind Then
MsgBox ("Value not found")
Sheets(currentSheet).Activate
End If
End Sub


I'm sort of stumped on what to do here. I have tested that this works to open a file and search but I don't know what to do next. If what I'm looking to do just can't be done, please let me know or if it can, how would I go about doing it?

I am running Windows XP and Excell 2007 and it may be helpful to note that Windows is really not my strong suit as this is a work computer and work is the only time I use Windows, at home I only use Linux

 

[Qubit]

I feel like I may not have been clear enough on what I want the macro to do and if this should be an edit instead of a reply then I apologize. What I want is to be able to enter the keyboard shortcut for the macro when I first open Excel and have a select file dialog box open up. Then once I've selected the file to open, it opens the file and brings up a search dialog box. Then if I want to search my name for example, I would type "Max" and it would bring me to the column containing a cell that has the word "Max" in it and then collapse all of the empty cells in that column, making it easier for me to read.

In case anyone wants/needs to know, the reason I need this is because my company sends out a weekly invoice of each person and their billing hours for the previous week in spreadsheet form and I want to be able to jump directly to the column with my name at the top and be able to immediately see only the numbers I need without having to scroll through the names of all the other employees across the country to find my name (they aren't alphabetical), and there are always just weird gaps between rows with data in them for some reason that make it hard to read.

 

[Qubit]

I am at the point now where it will even show the dropdown menu for what to filter but doesn't actually autofilter the blank cells out. Can anyone tell me what I'm doing wrong with the autofilter syntax? My code is:


Sub Get_Data()


FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a file to import", _
FileFilter:="Excel Files *.xls (*.xls),")
''
If FileToOpen = False Then
MsgBox "No file specified.", vbExclamation, "Duh!!!"
Exit Sub
Else
Workbooks.Open Filename:=FileToOpen
End If

Dim datatoFind
Dim sheetCount As Integer
Dim counter As Integer
Dim currentSheet As Integer

On Error Resume Next
currentSheet = ActiveSheet.Index
datatoFind = InputBox("Please enter the value to search for")
If datatoFind = "" Then Exit Sub
sheetCount = ActiveWorkbook.Sheets.Count
If IsError(CDbl(datatoFind)) = False Then datatoFind = CDbl(datatoFind)
For counter = 1 To sheetCount
Sheets(counter).Activate
Cells.Find(What:=datatoFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
If ActiveCell.Value = datatoFind Then ActiveCell.EntireColumn.Select
If ActiveCell.EntireColumn.Select Then Selection.AutoFilter
ActiveSheet.Range(ActiveCell.EntireColumn).AutoFilter Field:=1, Criteria1:="<>"
Exit Sub
Next counter
If ActiveCell.Value <> datatoFind Then
MsgBox ("Value not found")
Sheets(currentSheet).Activate
End If
End Sub