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

 

[Joe4]

I was hoping for an image (as described here in Section B: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html), as I cannot download internet files from my current location (workplace security policy).

However, I think I may see what is going on. That code is selecting a cell, which then calls your Worksheet_SelectionChange macro. Do you want this new code to run WITHOUT automatically activating that other code. So you will temporarily need to disable events, by modifying the Worksheet_Activate code like this:

Private Sub Worksheet_Activate()
    Application.EnableEvents = False
    Range("A29").End(xlUp).Offset(1, 0).Select
    Application.EnableEvents = True
End Sub

When using different event procedures (VBA code that is automatically run), it is important to be aware of all the different ones you are using and how they might interact with each other (one calls the other). It is quite easy to get in an endless loop if you aren't careful!

 

[ipbr21054]

Hi,
So does that mean when i first open this sheet the code will select the first available cell without a value in column A then when i work on the sheet my Worksheet_SelectionChange code will kick in and do what it is supposed to do.
Basically it wont work upon coming to this sheet ?

If so i have applied the code above but cell A29 is still selected.

 

[ipbr21054]

I suspect A4:A28 are part of a table with blanks
if so, maybe this

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

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

 

[Joe4]

So does that mean when i first open this sheet the code will select the first available cell without a value in column A then when i work on the sheet my Worksheet_SelectionChange code will kick in and do what it is supposed to do.
Basically it wont work upon coming to this sheet ?

Private Sub Worksheet_Activate()
    Application.EnableEvents = False
    Range("A29").End(xlUp).Offset(1, 0).Select
    Application.EnableEvents = True
End Sub

What this basically says in when the worksheet is first activated, temporarily disable event procedures (other automated code) from running.
Then, find our first blank cell in A4:A29.
Then re-enable automated events again.

So after that happens, if you select any other cells, your Worksheet_SelectionChange code will run.

 

[ipbr21054]

Joe4 that works apart from when i first come to this sheet as it still selects cell A29 even though cells A20 onwards are empty

 

[Joe4]

So, when does it work, versus when it doesn't work?
Is it when the workbook is first opened up that it is not working (if that is not already the active sheet)?

I recommend putting break points at the top of all your event procedures. Then test it out by doing what you do, and see what happens. As it enters into the VBA code, it will stop at the breakpoint. So you will be able to see exactly what code it is hitting and when. If you hit run after it stops, it will continue running until it hits the next break point.

 

[ipbr21054]

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

If i then start clicking around the page then the other code starts to work in respect of the coloured rows / columns etc.

I did this.
Started a new blank sheet & added the code you supplied.
It worked.

I then took a section of code at a time from this non working sheet 7 copied it to the new sheet,it still worked.
I did this until all the code was transfered over,it still worked.

Strange.

I then deleted the code on the non working sheet & then copied all this over,it then failed but the new sheet was still working.
I also then tried to delete one piece of code until it worked correctly,that failed and all i left was you code.

Really strange

 

[Joe4]

I believe that the issue is when each event procedure is being called. If you try doing what I suggsested, you will be able to see what is being called, and it may make more sense, regarding what is happening.
Or, instead of breakpoints, simply add Message Boxes to to top of all your procedures that tell you what is running, i.e.

Private Sub Worksheet_Activate()
[COLOR=#ff0000]    MsgBox "The Worksheet_Activate code is running"[/COLOR]
    Application.EnableEvents = False
    Range("A29").End(xlUp).Offset(1, 0).Select
    Application.EnableEvents = True
End Sub

Add similar MsgBox's to your other procedures, changing the value it returns. Then test it out, and see which procedures are running and in what order.
This is the first step, figure out exactly what is happening. Then once we know that, we can try to determine why.

 

[Joe4]

One thing I just tested and discovered which may explain things.
The "Worksheet_Activate" code only runs if you select another sheet in the same workbook, and then come back to it (or if you start on another sheet in that workbook, then go to that sheet).
It does NOT run if you only have one sheet in your workbook OR if you open the workbook and it is already on that sheet.
If you also want it to run when you open the workbook, you will need to add similar code to the Workbook_Open event.

 

[ipbr21054]

I will take a look at it.
My workbook consists of say 8 worksheets etc

Thanks