VBA move to next available row

B

blossomthe2nd

Active Member
Joined
Oct 11, 2010
Messages
450
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 :

Code: 
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 ?
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
Try adding this to your code:

Code: 
    Dim LR As Long
    LR = Range("A" & Rows.Count).End(xlUp).Row + 1
    Application.Goto Range("A" & LR), True
 
Upvote 0
Thanks Andrew,

This works so far as it moves the curser once a cell in A is populated, however, as they must input across a few different columns I may need something slightly different,

Is it possible to use this function but perhaps link it to cell B2.

Once cell B2 is amended move curser to next available row and move rows beneath header as what your code does ?

Can you help me with that ?

Thanks so much.
A
 
Upvote 0
(Apologies I previously said B2)

Cell B1 has a dropdown list of activities, rollovers, out of course,drawdowns and customer service.
Once a specific drop down is selected the code hides certain columns (as dependant on selection on particular columns need to be updated)

I wish,

After selecting from B2 , the curser will automatically move to the next available row and move this row up directly under the header on row 2.

Without doing this the spreadsheet looks patchy and not really user friendly ,

Can you help please ?

Thanks
A
 
Upvote 0
Hi Andrew,

At the moment when I put it in see below ,
Code: 
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
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
 Dim LR As Long
    LR = Range("A" & Rows.Count).End(xlUp).Row + 1
    Application.Goto Range("A" & LR), True
End Sub

When I change B1 the sheet formats to next available row ( perfect), but once I update the cell , say A14, It then pulls the curser to A15 and will not let me update within the columns , A14,B14,D14 etc .

I would like to select from drop down , the sheet to hide columns and curser to go to next available row for data entry accross valid columns . Then for me to continue to enter data and only when I select a different drop down should the auto formating sheet start .

Thanks so much for your help

A
 
Upvote 0
You need to use the Worksheet_Change event procedure, exiting if Target is not B1. Try the untested:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> "$B$1" Then Exit Sub
    Dim LR As Long
    If InStr("out_of_course drawdowns customer_service", LCase(Target.Value)) > 0 Then
        Columns("C:G").EntireColumn.Hidden = True
    Else
        Columns("C:G").EntireColumn.Hidden = False
    End If
    If InStr("rollovers", LCase(Target.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(Target.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(Target.Value)) > 0 Then
        Columns("O:q").EntireColumn.Hidden = True
    Else
        Columns("O:q").EntireColumn.Hidden = False
    End If
    If InStr("customer_service rollovers", LCase(Target.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(Target.Value)) > 0 Then
        Columns("s:Z").EntireColumn.Hidden = True
    Else
        Columns("s:Z").EntireColumn.Hidden = False
    End If
    If InStr("rollovers drawdowns", LCase(Target.Value)) > 0 Then
        Columns("h").EntireColumn.Hidden = True
    Else
        Columns("h").EntireColumn.Hidden = False
    End If
    LR = Range("A" & Rows.Count).End(xlUp).Row + 1
    Application.Goto Range("A" & LR), True
End Sub
 
Upvote 0
Hi Andrew,

This will ulitmately be a shared document with multiple users. I am afraid that we will loose some data .

Is it possible to put an Autosave function on cell B1, so when we select from the drop down it autosaves workbook.

Thanks again for all your help
A
 
Upvote 0
You must log in or register to reply here.

Similar threads

Y
  • Question Question
How to automatically hide/unhide columns based on related month with VBA
Replies
1
Views
363
ysfuyar
Y
A
  • Solved
doesn't show data in listbox on form without any error in this code
Replies
6
Views
134
Abdo
A
welshraz
  • Question Question
VBA - Restrict Code to One Column for Multi-choice Dropdown
Replies
2
Views
115
welshraz
welshraz
G
  • Solved
run VBA when clicked
Replies
4
Views
142
Gylle
G
D
  • Question Question
Make the code work in a protected sheet
Replies
7
Views
180
dmt32
D

Forum statistics

Threads
1,224,598
Messages
6,179,818
Members
452,946
Latest member
JoseDavid

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top