Find First Empty Row

Jamm_027

Board Regular
Joined
Sep 10, 2007
Messages
249
I am trying to copy and past a selection into the first empty row of a database. I walked thru my code using F8 and it works right up to the very end where I have "Activesheet.Paste". Instead of pasting in the first empty row. It pastes over the previous data.

Code:
Private Sub CommandButton1_Click()

Application.ScreenUpdating = False

'Place all data from userform1 to Tables

Sheets("Sheet2").Range("aa1").Value = UserForm1.ComboBox1.Value
Sheets("Sheet2").Range("ab1").Value = UserForm1.ComboBox2.Value
Sheets("Sheet2").Range("AC1").Value = UserForm1.ComboBox3.Value

Unload Me

Sheets("View").Select
Range("A4:P10000").Select
Selection.ClearContents

'Filter combobox 1

    Sheets("Download").Select
    Range("A1:O5000").Select
    Selection.AutoFilter

    If Sheets("Sheet2").Range("AA1").Value <> "" Then
    Selection.AutoFilter Field:=1, Criteria1:=Sheets("Sheet2").Range("AA1").Value
    Else
    Selection.AutoFilter Field:=1
    End If
    
 'Copy and Paste Filter 1
    Range("A1:P1000").Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Sheets("View").Select
    Range("A3").Select
    ActiveSheet.Paste
    
'Filter Combobox 2

    Sheets("Download").Select
    Range("A1:O5000").Select
    Selection.AutoFilter

    If Sheets("Sheet2").Range("AB1").Value <> "" Then
    Selection.AutoFilter Field:=1, Criteria1:=Sheets("Sheet2").Range("AB1").Value
    Else
    Selection.AutoFilter Field:=1
    End If
    
 'Copy and Paste Filter 2
    Range("A1:P1000").Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Sheets("View").Select
     
    Dim iRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("View")

'find first empty row in database
    
    iRow = ws.Cells(Rows.Count, 1) _
    .End(xlUp).Offset(1, 0).Row
    ActiveSheet.Paste

Application.ScreenUpdating = True
    
End Sub
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Code:
Private Sub CommandButton1_Click()

    Application.ScreenUpdating = False
    
    'Place all data from userform1 to Tables
    
    Sheets("Sheet2").Range("aa1").Value = UserForm1.ComboBox1.Value
    Sheets("Sheet2").Range("ab1").Value = UserForm1.ComboBox2.Value
    Sheets("Sheet2").Range("AC1").Value = UserForm1.ComboBox3.Value
    
    Unload Me
    
    Sheets("View").Range("A4:P10000").ClearContents
    
    'Filter combobox 1

    With Sheets("Download").Range("A1:O5000")
    
        .AutoFilter

        If Sheets("Sheet2").Range("AA1").Value <> "" Then
            .AutoFilter Field:=1, Criteria1:=Sheets("Sheet2").Range("AA1").Value
        Else
                .AutoFilter Field:=1
        End If
    
 'Copy and Paste Filter 1
        .Range("A1:P1000").SpecialCells(xlCellTypeVisible).Copy Sheets("View").Range("A3")
    
'Filter Combobox 2
        .AutoFilter

        If Sheets("Sheet2").Range("AB1").Value <> "" Then
            .AutoFilter Field:=1, Criteria1:=Sheets("Sheet2").Range("AB1").Value
        Else
            .AutoFilter Field:=1
        End If
    
'find first empty row in database
    Dim ws As Worksheet
    Set ws = Worksheets("View")
    iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    Dim iRow As Long
'Copy and Paste Filter 2
        .Range("A1:P1000").SpecialCells(xlCellTypeVisible).Copy Sheets("View").Cells(iRow, "A")
    End With

Application.ScreenUpdating = True
    
End Sub
 
Upvote 0
xld,

I got an error when I ran the code. It says that the following line is a duplicate declaration.

Dim iRow As Long

jamm
 
Upvote 0
Sorry, my error

Code:
Private Sub CommandButton1_Click()

    Application.ScreenUpdating = False
    
    'Place all data from userform1 to Tables
    
    Sheets("Sheet2").Range("aa1").Value = UserForm1.ComboBox1.Value
    Sheets("Sheet2").Range("ab1").Value = UserForm1.ComboBox2.Value
    Sheets("Sheet2").Range("AC1").Value = UserForm1.ComboBox3.Value
    
    Unload Me
    
    Sheets("View").Range("A4:P10000").ClearContents
    
    'Filter combobox 1

    With Sheets("Download").Range("A1:O5000")
    
        .AutoFilter

        If Sheets("Sheet2").Range("AA1").Value <> "" Then
            .AutoFilter Field:=1, Criteria1:=Sheets("Sheet2").Range("AA1").Value
        Else
                .AutoFilter Field:=1
        End If
    
 'Copy and Paste Filter 1
        .Range("A1:P1000").SpecialCells(xlCellTypeVisible).Copy Sheets("View").Range("A3")
    
'Filter Combobox 2
        .AutoFilter

        If Sheets("Sheet2").Range("AB1").Value <> "" Then
            .AutoFilter Field:=1, Criteria1:=Sheets("Sheet2").Range("AB1").Value
        Else
            .AutoFilter Field:=1
        End If
    
'find first empty row in database
    Dim ws As Worksheet
    Dim iRow As Long
    Set ws = Worksheets("View")
    iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
'Copy and Paste Filter 2
        .Range("A1:P1000").SpecialCells(xlCellTypeVisible).Copy Sheets("View").Cells(iRow, "A")
    End With

Application.ScreenUpdating = True
    
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

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