Locate the first empty cell in a row and then a column

[markswjh]

Hello again

I am attempting to have a piece code enter a name in the first blank cell of the last column in use. For some reason the code I have keeps overwriting the previous cell. It also keeps adding the entries to the first column. The issue is this code needs to enter the data into the last column (what ever that may be), rather than the first column each time. I know the error I am making, I just don't know how to take it from Cells(Columns.Count, 1) to a variable which will change based on the most recently used column.

can anyone help with this?

Public Sub DetentionListAddName() 
    Application.ScreenUpdating = False
    'Select Detentions list
    Sheets("Detentions List").Select
    'Select the first cell in column A
    Range("A1").Select
    'Copy Current Date Title
    ActiveCell.Copy
    'Find the last cell in column A that has data
    Cells(Columns.Count, 1).End(xlUp).Offset(0, 2).Select
    'Paste Title
    Selection.PasteSpecial Paste:=xlPasteValues
    'Select the sheet named "Submition Forms"
    Sheets("Submition Forms").Select
    'Select cell A4
    Range("A4").Select

    'Start the loop that will run until the active cell is empty
    Do Until IsEmpty(ActiveCell)
        'Select Cell to columns to the right
        ActiveCell.Offset(0, 2).Select
        'Check the value of the active cell
        Select Case ActiveCell
            Case "N"
                'Copy the cell two columns to the left
                ActiveCell.Offset(0, -2).Copy
                'Go to the sheet named "Detentions List"
                Sheets("Detentions List").Select
                'Select the first cell in column A
                Range("A1").Select
                'Find the last cell in column A that has data
                Cells(Columns.Count, 1).End(xlUp).Offset(0, 2).Select
                'Select the next empty cell in column A
                'ActiveCell.End(xlUp).Select
                'ActiveCell.Offset(1, 0).Select
                Cells(Columns.Count, 1).End(xlUp).Offset(1, 0).Select
                'Paste the copied cell in the selected empty cell
                Selection.PasteSpecial
                'Go back to the sheet named "Submition Forms"
                Sheets("Submition Forms").Select
            Case "Y"
                'If the value is "Y", do nothing
        End Select
        'Move to the next cell in column C
        
        ActiveCell.Offset(1, -2).Select
    Loop
Sheets("Detentions List").Select
Call FmtDetentions
Sheets("Submition Forms").Select
'select top cell
Range("A4").Select
'Reactivate screen updating
Application.ScreenUpdating = True

End Sub

Thanks all

 

[DanteAmor]

I don't understand what you need because the concepts are not clear to me.
But let's start with the basics:
To find the last row with data from a column, there are several ways, I show you some:

Sub lastRow_fromColumn()
  Dim lr As Long
  lr = Range("A" & Rows.Count).End(xlUp).Row
  MsgBox lr
End Sub

Sub lastRow_fromColumn_2()
  Dim lr As Long
  lr = Cells(Rows.Count, 1).End(xlUp).Row
  MsgBox lr
End Sub

Sub lastRow_fromColumn_3()
  Dim lr As Long
  lr = Cells(Rows.Count, "A").End(xlUp).Row
  MsgBox lr
End Sub

Sub lastRow_fromColumn_4()
  Dim lr As Long
  lr = Cells(Rows.Count, "A").End(3).Row
  MsgBox lr
End Sub

Sub lastRow_fromColumn_5()
  Dim lr As Long
  Dim f As Range
  Set f = Range("A:A").Find("*", , xlValues, xlPart, xlByRows, xlPrevious)
  lr = f.Row
  MsgBox lr
End Sub

In the following example, all of the above macros return a 7 as a result.

varios 13ene2023.xlsm
	A	B
1	Data
2	sd
3	df
4	fg
5	gh
6	jk
7	lk	<<- last row with data of column A = 7
8
Data1

If you want the first empty cell, from column A, after the last cell with data, you can simply add 1, for example:

Sub lastRow_fromColumn()
  Dim lr As Long
  lr = Range("A" & Rows.Count).End(xlUp).Row + 1
  MsgBox lr
End Sub

Sub lastRow_fromColumn_2()
  Dim lr As Long
  lr = Cells(Rows.Count, 1).End(xlUp).Row + 1
  MsgBox lr
End Sub

So the first empty row, from column A, after the last cell with data is row 8.
There are other ways to find that number 8, but I think that the previous ones are the most used and the most efficient.

----------------

Now, to find the last column with data of a row, you can use one of the following examples:

Sub lastColumn_fromRow_ex1()
  Dim lc As Long
  lc = Cells(1, Columns.Count).End(xlToLeft).Column
  MsgBox lc
End Sub

Sub lastColumn_fromRow_ex2()
  Dim lc As Long
  lc = Cells(1, Columns.Count).End(1).Column
  MsgBox lc
End Sub

Sub lastColumn_fromRow_ex3()
  Dim lc As Long
  Dim f As Range
  Set f = Range("1:1").Find("*", , xlValues, xlPart, xlByColumns, xlPrevious)
  lc = f.Column
  MsgBox lc
End Sub

In the following example, all of the above macros return a 5 as a result.

varios 13ene2023.xlsm
	A	B	C	D	E	F
1	Head1	Head2	Head3	Head4	Head5
2					/\
3					|
4					Last column with data of row 1 = 5
5
Data2

If you want the first empty cell, from row 1, after the last cell with data, you can simply add 1, for example:

Sub lastColumn_fromRow_ex1()
  Dim lc As Long
  lc = Cells(1, Columns.Count).End(xlToLeft).Column + 1
  MsgBox lc
End Sub

Sub lastColumn_fromRow_ex2()
  Dim lc As Long
  lc = Cells(1, Columns.Count).End(1).Column + 1
  MsgBox lc
End Sub

Sub lastColumn_fromRow_ex3()
  Dim lc As Long
  Dim f As Range
  Set f = Range("1:1").Find("*", , xlValues, xlPart, xlByColumns, xlPrevious)
  lc = f.Column + 1
  MsgBox lc
End Sub

So the first empty column, from row 1, after the last cell with data is column 6.

-------------------
Try out the macros with the examples for you to practice.
With that information, I hope it helps you find what you need.