VBA move to next available row

[blossomthe2nd]

Hi Guys

Much to the help of previous queries I wrote a VBA code that shows certain columns dependant on value in cell B1, see below :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If InStr("out_of_course drawdowns customer_service", LCase(Range("B1").Value)) > 0 Then
Columns("C:G").EntireColumn.Hidden = True
Else
Columns("C:G").EntireColumn.Hidden = False
End If
If InStr("rollovers", LCase(Range("B1").Value)) > 0 Then
Columns("i:K").EntireColumn.Hidden = True
Else
Columns("i:K").EntireColumn.Hidden = False
End If
If InStr("out_of_course drawdowns rollovers", LCase(Range("B1").Value)) > 0 Then
Columns("L:N").EntireColumn.Hidden = True
Else
Columns("L:N").EntireColumn.Hidden = False
End If
If InStr("customer_service drawdowns rollovers", LCase(Range("B1").Value)) > 0 Then
Columns("O:q").EntireColumn.Hidden = True
Else
Columns("O:q").EntireColumn.Hidden = False
End If
If InStr("customer_service rollovers", LCase(Range("B1").Value)) > 0 Then
Columns("R").EntireColumn.Hidden = True
Else
Columns("R").EntireColumn.Hidden = False
End If
If InStr("customer_service rollovers out_of_course", LCase(Range("B1").Value)) > 0 Then
Columns("s:Z").EntireColumn.Hidden = True
Else
Columns("s:Z").EntireColumn.Hidden = False
End If
If InStr("rollovers drawdowns", LCase(Range("B1").Value)) > 0 Then
Columns("h").EntireColumn.Hidden = True
Else
Columns("h").EntireColumn.Hidden = False
End If
End Sub

My problem is , this is for a type of Form , so in the worksheet I have frozen the column header to row 2.

What I would like to happen is after the above columns have been hidden the curser will go to the next available row and (if possible ) move this row up directly beneath the frozen headers !!

Can anyone help ?

 

[Andrew Poulsom]

I wouldn't recommend sharing a workbook, but you can try putting:

ThisWorkbook.Save

in your current procedure.

 

[blossomthe2nd]

Thanks Andrew,

Out of curiosity why wouldn't you reccommend Sharing?

This is a log of activities that a team completes, they fill out the excel work book throughout the day , but many users would use the same log ,

Is there a better way to do this ?

Thanks

Anne

 

[Andrew Poulsom]

They are prone to loss of data:

http://excel.tips.net/Pages/T002998_Losing_Data_in_a_Shared_Workbook.html

http://blog.contextures.com/archives/2008/11/18/avoiding-shared-workbooks-in-excel/