How to paste data in column in first empty row?

Question

I am looking for a solution to paste data into the first empty row in specific columns.

The following function copies and pastes data properly but not in the first empty row in specific columns.

Sub Test()

Application.ScreenUpdating = False
Dim i As Integer
Dim Lastrow As Long

Lastrow = Cells(Rows.Count, "A").End(xlUp).Row

For i = 1 To Lastrow

    If Cells(i, 1).Value = "cav. 1" Then

        Cells(i, 11).Offset(0, 10).Value = Cells(i, 11).Value
        
    End If

    If Cells(i, 1).Value = "cav. 2" Then
    Cells(i, 11).Offset(0, 11).Value = Cells(i, 11).Value
    End If
    
Next
Application.ScreenUpdating = True
End Sub

Hello Wojtek - do you want the data to be placed in the first blank column in row 1? For example if there are two column headers then you want to start your procedure in C1? Or - are you placing data in column A, but want to start after the last row with data? — JimT
– JimT, Commented Jan 13, 2022 at 20:58

VBasic2008 · Accepted Answer · 2022-01-13 21:25:40Z

Copy to Other Columns, Not in the Same Row

Option Explicit

Sub Test()
    
    ' Source
    Const sfRow As Long = 2 ' First Row
    Const slCol As String = "A" ' lookup
    Const srCol As String = "K" ' result
    Const sCrit1 As String = "cav. 1"
    Const sCrit2 As String = "cav. 2"
    
    ' Destination
    Const dfRow As Long = 2 ' First Row
    Const dCol1 As String = "U"
    Const dCol2 As String = "V"

    Application.ScreenUpdating = False

    Dim ws As Worksheet: Set ws = ActiveSheet ' improve
    
    Dim slRow As Long: slRow = ws.Cells(ws.Rows.Count, slCol).End(xlUp).Row
    Dim dr1 As Long: dr1 = dfRow
    Dim dr2 As Long: dr2 = dfRow
    
    Dim sr As Long
    
    For sr = sfRow To slRow
        Select Case LCase(CStr(ws.Cells(sr, slCol).Value))
        Case sCrit1
            ws.Cells(dr1, dCol1).Value = ws.Cells(sr, srCol).Value
            dr1 = dr1 + 1
        Case sCrit2
            ws.Cells(dr2, dCol2).Value = ws.Cells(sr, srCol).Value
            dr2 = dr2 + 1
        End Select
    Next sr

    Application.ScreenUpdating = True

End Sub

Collectives™ on Stack Overflow

How to paste data in column in first empty row?

1 Answer 1

Copy to Other Columns, Not in the Same Row

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Copy to Other Columns, Not in the Same Row

Comments

Your Answer

Sign up or log in

Post as a guest

Related