Select next available cell without any value in it

[ipbr21054]

Afternoon,
I currently using this code below so it runs when i open the worksheet.

Private Sub Worksheet_Activate()Range("A4").Activate
End Sub

I am only interested in Column A
The range is ALWAYS A4:A28

What i would like please "as opposed to going to A4" is that when i open the worksheet the next available cell in column A without and value etc is selected.

So example would be cell A22 has a value in it so it would then select cell A23


The code for the page is currently shown below

Private Sub CommandButton1_Click()  Sheets("EXPENSES (2)").Range("D4").Value = Sheets("EXPENSES (1)").Range("D30").Value
  Sheets("EXPENSES (2)").Range("F4:K4").Value = Sheets("EXPENSES (1)").Range("F30:K30").Value
  Sheets("EXPENSES (2)").Activate
  ActiveSheet.Range("A5").Select
  If Sheets("EXPENSES (2)").Range("K32").Value <> Sheets("EXPENSES (1)").Range("K32").Value Then MsgBox "Balance of sheets incorrect", vbCritical, "K32 CELLS DO NOT MATCH"
End Sub
Private Sub CommandButton2_Click()
Dim Answer As Long, wb As Workbook
    Answer = MsgBox("Transfer Values To Summary Sheet ?", vbYesNo + vbInformation, "End Of Month Accounts")
    If Answer = vbYes Then
        Set wb = Workbooks.Open(Filename:="C:\Users\Ian\Desktop\EBAY\ACCOUNTS\CURRENT SHEETS\SUMMARY SHEET.xlsm")
        Workbooks("ACCOUNTS.xlsm").Sheets("EXPENSES (1)").Range("D30").Copy
        wb.Sheets("Sheet1").Range("I28").PasteSpecial xlPasteValues
        
        Workbooks("ACCOUNTS.xlsm").Sheets("EXPENSES (1)").Range("F30").Copy
        wb.Sheets("Sheet1").Range("I29").PasteSpecial xlPasteValues
        
        Workbooks("ACCOUNTS.xlsm").Sheets("EXPENSES (1)").Range("G30").Copy
        wb.Sheets("Sheet1").Range("I30").PasteSpecial xlPasteValues
        
        Workbooks("ACCOUNTS.xlsm").Sheets("EXPENSES (1)").Range("H30").Copy
        wb.Sheets("Sheet1").Range("I31").PasteSpecial xlPasteValues
        
        Workbooks("ACCOUNTS.xlsm").Sheets("EXPENSES (1)").Range("I30").Copy
        wb.Sheets("Sheet1").Range("I32").PasteSpecial xlPasteValues
        
        Workbooks("ACCOUNTS.xlsm").Sheets("EXPENSES (1)").Range("J30").Copy
        wb.Sheets("Sheet1").Range("I33").PasteSpecial xlPasteValues
        
        Workbooks("ACCOUNTS.xlsm").Sheets("EXPENSES (1)").Range("K30").Copy
        wb.Sheets("Sheet1").Range("I34").PasteSpecial xlPasteValues
        wb.Close True


        End If
        Workbooks("ACCOUNTS.xlsm").Sheets("EXPENSES (1)").Range("A4").Select
        Application.CutCopyMode = False
        MsgBox "Summary Transfer Completed", vbInformation, "SUCCESSFUL MESSAGE"
        ActiveWorkbook.Save
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim myStartCol As String
    Dim myEndCol As String
    Dim myStartRow As Long
    Dim myLastRow As Long
    Dim myRange As Range


    If Target.Cells.Count > 1 Then Exit Sub
    
    Application.ScreenUpdating = False
    
'   *** Specify columns to apply this to ***
    myStartCol = "A"
    myEndCol = "K"


'   *** Specify start row ***
    If (Target.Row > 3 And Target.Row < 29) Then
          myStartRow = 4
    Else: myStartRow = 29
    End If
'   Use first column to find the last row
    If (Target.Row > 3 And Target.Row < 29) Then
          myLastRow = 28
    Else: myLastRow = 30
    End If
    
'   Build range to apply this to
    Set myRange = Range(Cells(myStartRow, myStartCol), Cells(myLastRow, myEndCol))
    
'   Clear the color of all the cells in range
    Range("A4:K30").Interior.ColorIndex = 2
    
'   Check to see if cell selected is outside of range
    If Intersect(Target, myRange) Is Nothing Then Exit Sub
    
'   This color will Highlight the row
    If (Target.Row > 3 And Target.Row < 29) Then
    Range(Cells(Target.Row, myStartCol), Cells(Target.Row, myEndCol)).Interior.ColorIndex = 8
'   This color will Highlight the column
    Range(Cells(4, Target.Column), Cells(28, Target.Column)).Interior.ColorIndex = 8
    Else
    Range(Cells(Target.Row, myStartCol), Cells(Target.Row, myEndCol)).Interior.ColorIndex = 3
    End If
'   This color will Highlight the cell in the row
    If (Target.Row > 3 And Target.Row < 29) Then
    Target.Interior.Color = vbGreen
    Else
    Target.Interior.Color = vbRed
    End If
    Application.ScreenUpdating = True


End Sub
Private Sub Worksheet_Change(ByVal Target As Range)


'   Exit if more than one cell updated at a time
    If Target.Count > 1 Then Exit Sub


'   Check to see if value updated is in column B or D
    If Target.Column = 2 Or Target.Column = 4 Then
        Application.EnableEvents = False
        If UCase(Cells(Target.Row, "B")) = "REFUND" Then
            Cells(Target.Row, "D") = Abs(Cells(Target.Row, "D")) * -1
        Else
            If Cells(Target.Row, "B") = "" Then Cells(Target.Row, "D").ClearContents
        End If
        Application.EnableEvents = True
    End If
    If Not (Application.Intersect(Target, Range("A3:K28")) _
      Is Nothing) Then
        With Target
            If Not .HasFormula Then
                Application.EnableEvents = False
                .Value = UCase(.Value)
                Application.EnableEvents = True
            End If
        End With
    End If
End Sub


Private Sub Worksheet_Activate()
Range("A4").Activate
End Sub

 

[NoSparks]

Please advise where i need to put this so i can try it thanks

Private Sub Worksheet_Activate()
    For i = 28 To 4 Step -1
        If Cells(i - 1, "A") <> "" Then
            Cells(i, "A").Select
            Exit For
        End If
    Next
End Sub

 

[NoSparks]

I open the sheet and cell A29 is selected but should be cell A24

If you mean when you open the workbook the sheet is active but the wrong cell selected
try this in the ThisWorkbook module

Private Sub Workbook_Open()
    Dim ws As Worksheet
Set ws = ActiveSheet
Application.ScreenUpdating = False
Sheets("LIST").Activate
ws.Activate
Application.ScreenUpdating = True
End Sub

 

[ipbr21054]

We might be getting somewhere.
I have tried this code below

Private Sub Worksheet_Activate()    For i = 28 To 4 Step -1
        If Cells(i - 1, "A") <> "" Then
            Cells(i, "A").Select
            Exit For
        End If
    Next
End Sub

It pretty much works apart from one thing & that is when i save & close the worksheet.

If i select any cell on the sheet in which i have the code the next empty cell is selected as it should be.
If i select a random cell say Z50 then select another worksheet then come back to this sheet the next empty cell is selected as it should be in this case A24

This is where it fails,
Select say cell C24 save close then reopen the sheet.
Cell C24 is still selected.
If i then select any cell on that sheet or just move to another sheet then come back to this sheet A24 is selected.

As long as i dont save/close all is ok.

With regards to the code advised in post #22 i have say 8 sheets in this workbook so am i supposed to change LIST with the sheet name,as if so i dont think this would work unless i duplicate the code 8 times and change that LIST in every code ?

 

[Joe4]

Select say cell C24 save close then reopen the sheet.
Cell C24 is still selected.

I already addressed that exact thing back in post 19.

Just add the similar code that you have in your Worksheet_Activate event procedure to the Workbook_Open event procedure.
That is found in the ThisWorkbook module, and the code would look like:

Private Sub Workbook_Open()

    If ActiveSheet.Name = [COLOR=#ff0000][B]"Sheet1"[/B][/COLOR] Then
        Application.EnableEvents = False
        Range("A29").End(xlUp).Offset(1, 0).Select
        Application.EnableEvents = True
    End If
    
End Sub

Note that you will just need to change the part in red to the sheet name that you want to apply this to. If it opens, and you are on some other sheet, you don't want it to run.

 

[NoSparks]

@ ipbr21054


If you use the Worksheet_Activate code from post 21 in the sheet module of each sheet you want this to occur on,
and the Workbook_Open code from post 22 in the ThisWorkbook module,
save and close the workbook, then re-open the workbook,
do things work the way you're wanting ?


I don't know what you've got in some of those A cells on most of the 12 sheets ("List" sheet excluded) but it definitely prevents the normal xlUp from operating as expected
although it will if you clear the contents of all those supposedly blank A cells, but not knowing how they got the way they are I don't know if they'll revert back.

 

[ipbr21054]

Hi,
I put a link to download the file,please take a look and you will see nothing is in those cells.

I agree its very strange

 

[ipbr21054]

@ ipbr21054


If you use the Worksheet_Activate code from post 21 in the sheet module of each sheet you want this to occur on,
and the Workbook_Open code from post 22 in the ThisWorkbook module,
save and close the workbook, then re-open the workbook,
do things work the way you're wanting ?


I don't know what you've got in some of those A cells on most of the 12 sheets ("List" sheet excluded) but it definitely prevents the normal xlUp from operating as expected
although it will if you clear the contents of all those supposedly blank A cells, but not knowing how they got the way they are I don't know if they'll revert back.

This has worked.

I have played with it for 15 minutes in respect of adding some dates down column A on each sheet then clicking a cell off the page.
Save,close then open & each page has the correct cell selected.

Many thanks for your help with this today.
Have a nice Friday everyone.