how to check last row from particular cell to down in vba excel?-CodePudding

My requirement is to find the last row from a particular cell and store the information in it. Again the next data save should be saved in its next row.

Private Sub CommandButton1_Click()
Dim sh As Worksheet
        Set sh = ThisWorkbook.Sheets("Sales_Bill")
        Dim lr As Long
        lr = sh.Range("B:B15" & Rows.Count).End(xlUp).Row

'''''''''''''''Validation'''''''''''''''
        
        If Me.txtiname.value = "" Then
           MsgBox "Please enter the iteam name", vbOKOnly   vbInformation
           Exit Sub
        End If            
    
'''''''''''''''''Add Data in Excel Sheet'''''''''''''
       
       With sh
             .Cells(lr   1, "B").value = Me.txtiname.value                
       End With
 
End Sub

CodePudding user response：

 Dim sh As Worksheet
        Set sh = ThisWorkbook.Sheets("Sheetname")
        Dim lr As Long
        lr = sh.Range("B33:B" & Rows.Count).End(xlUp).Row

'''''''''''''''Validation'''''''''''''''
        
        If Me.txtiname.value = "" Then
           MsgBox "Please enter the iteam name", vbOKOnly   vbInformation
           Exit Sub
        End If
        
    
'''''''''''''''''Add Data in Excel Sheet'''''''''''''
       
       With sh           
           .Cells(lr   1, "B").value = Me.txtiname.value               
       End With

End Sub

CodePudding user response：

First Available Row

A Quick Fix

Private Sub CommandButton1_Click()

    If Me.txtiname.Value = "" Then
       MsgBox "Please enter the iteam name", vbOKOnly   vbInformation
       Exit Sub
    End If

    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Sales_Bill")

    Dim lr As Long: lr = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row ' last
    Dim er As Long: er = IIf(lr < 15, 15, lr   1) ' first empty

    ws.Cells(er, "B").Value = Me.txtiname.Value
  
End Sub