VBA code to get data from another column.

[SamarthSalunkhe]

Hi All,

I am using below code to get data from respective cell, but I want code to get data from full column.

Means I am using below code for B1 cell only. But if want code should be check value of column ('B:B") from Sheet1 and take it in Shee2 column ("B:B").

Sub Select_Data()

If Sheet1.Range("B1").Value <> "" Then Sheet2.Range("B1").Value = Sheet1.Range("B1").Value


End Sub

can anyone help me with the code it will help me a lot.

Thank You

 

[abdelfattah]

maybe

Sub Select_Data()
Dim lr Long
lr = sheet1.Range("B" & Rows.Count).End(xlUp).Row
If Not IsEmpty(sheet1.Range("B1:B" & lr).Value) Then sheet2.Range("B1:B" & lr).Value = sheet1.Range("B1:B" & lr).Value

End Sub

 

[kevin9999]

If you have some data already in sheet2, column B, that you want to retain if the corresponding cell in sheet1 is blank, then try the following:

Option Explicit
Sub Copy_Non_Blanks_Only()
    Dim ArrIn1, ArrIn2, ArOut
    Dim LRow As Long, i As Long
    LRow = Sheet1.Cells(Rows.Count, "B").End(xlUp).Row
    ArrIn1 = Sheet1.Range("B1:B" & LRow)
    ArrIn2 = Sheet2.Range("B1:B" & LRow)
    ReDim ArrOut(1 To LRow, 1 To 1)
    
    For i = 1 To LRow
        If ArrIn1(i, 1) <> "" Then ArrOut(i, 1) = ArrIn1(i, 1) Else ArrOut(i, 1) = ArrIn2(i, 1)
    Next i
    Sheet2.Range("B1").Resize(LRow, 1).Value = ArrOut
End Sub

 

[abdelfattah]

@kevin9999
with make your code ignore blank cells among filled cell for sheet1 ,then the values in sheet2 will become arranged without any blank cells among values instead of contains blank cells among filled cells that will make the data inappropriate.

 

[kevin9999]

@kevin9999
with make your code ignore blank cells among filled cell for sheet1 ,then the values in sheet2 will become arranged without any blank cells among values instead of contains blank cells among filled cells that will make the data inappropriate.

I'm not sure if I follow you 100%, but I'll try to explain the logic of my code. Firstly, as I explained in bold in my post, it really depends on whether the OP wants to retain any existing data in sheet 2 column B. If they want to retain existing data AND the corresponding cell IS blank, then the existing data is kept.
If the OP wants to remove/delete any existing data on sheet 2, irrespective of whether the corresponding cell on sheet1 is blank, then I can easily modify the code.
I'll wait for a response from the OP to see which way they want to go.
Kind Regards
Kevin

 

[SamarthSalunkhe]

If you have some data already in sheet2, column B, that you want to retain if the corresponding cell in sheet1 is blank, then try the following:

Option Explicit
Sub Copy_Non_Blanks_Only()
    Dim ArrIn1, ArrIn2, ArOut
    Dim LRow As Long, i As Long
    LRow = Sheet1.Cells(Rows.Count, "B").End(xlUp).Row
    ArrIn1 = Sheet1.Range("B1:B" & LRow)
    ArrIn2 = Sheet2.Range("B1:B" & LRow)
    ReDim ArrOut(1 To LRow, 1 To 1)
   
    For i = 1 To LRow
        If ArrIn1(i, 1) <> "" Then ArrOut(i, 1) = ArrIn1(i, 1) Else ArrOut(i, 1) = ArrIn2(i, 1)
    Next i
    Sheet2.Range("B1").Resize(LRow, 1).Value = ArrOut
End Sub

@kevin9999

Thank you for your time, I want to collect as it is date in another sheet.

 

[SamarthSalunkhe]

maybe

Sub Select_Data()
Dim lr Long
lr = sheet1.Range("B" & Rows.Count).End(xlUp).Row
If Not IsEmpty(sheet1.Range("B1:B" & lr).Value) Then sheet2.Range("B1:B" & lr).Value = sheet1.Range("B1:B" & lr).Value

End Sub

@abdelfattah

Code is meeting my expectations.

Thank you so much for your help.

 

[SamarthSalunkhe]

@abdelfattah

Code is meeting my expectations.

Thank you so much for your help.

Hi @abdelfattah

I have tried to apply below changes in your code, but I am getting error.

actually, I want to collect value excluding header line, could you please help me with rectification?

Sub Select_Data()

    Dim lr As Long

    lr = Sheet3.Range("A" & Rows.Count).End(xlUp).Row
    If Not IsEmpty(Sheet3.Range("A2:A" & lr).Value) Then Sheet1.Range("C8:C" & lr).Value = "NEFT"

    lr = Sheet3.Range("B" & Rows.Count).End(xlUp).Row
    If Not IsEmpty(Sheet3.Range("B2:B" & lr).Value) Then Sheet1.Range("B8:B" & lr).Value = Sheet3.Range("B2:B" & lr).Value

    lr = Sheet3.Range("B" & Rows.Count).End(xlUp).Row
    If Not IsEmpty(Sheet3.Range("B2:B" & lr).Value) Then Sheet1.Range("D8:D" & lr).Value = Sheet3.Range("D2:D" & lr).Value

    lr = Sheet3.Range("C" & Rows.Count).End(xlUp).Row
    If Not IsEmpty(Sheet3.Range("C2:C" & lr).Value) Then Sheet1.Range("E8:E" & lr).Value = Sheet3.Range("C2:C" & lr).Value

    lr = Sheet3.Range("D" & Rows.Count).End(xlUp).Row
    If Not IsEmpty(Sheet3.Range("D2:D" & lr).Value) Then Sheet1.Range("K8:K" & lr).Value = Sheet3.Range("D2:D" & lr).Value

    lr = Sheet3.Range("E" & Rows.Count).End(xlUp).Row
    If Not IsEmpty(Sheet3.Range("E2:E" & lr).Value) Then Sheet1.Range("A8:A" & lr).Value = Sheet3.Range("E2:E" & lr).Value

    lr = Sheet3.Range("F" & Rows.Count).End(xlUp).Row
    If Not IsEmpty(Sheet3.Range("F2:F" & lr).Value) Then Sheet1.Range("F8:F" & lr).Value = Sheet3.Range("F2:F" & lr).Value

    lr = Sheet3.Range("G" & Rows.Count).End(xlUp).Row
    If Not IsEmpty(Sheet3.Range("G2:G" & lr).Value) Then Sheet1.Range("G8:G" & lr).Value = Sheet3.Range("G2:G" & lr).Value

    lr = Sheet3.Range("K" & Rows.Count).End(xlUp).Row
    If Not IsEmpty(Sheet3.Range("K2:K" & lr).Value) Then Sheet1.Range("R2:R" & lr).Value = Sheet3.Range("K2:K" & lr).Value

End Sub