Need to Concatenate two columns by a macro

rsami

New Member
Joined
Nov 17, 2011
Messages
17
Hi all,
I'm new to here,
my question should be explain with a excel sheet.but i can't find any option to attach a page.
so i upload the page to file shearing site and here is the link for below described problem
beni help.xlsx

I need to concatenate B and C columns in beni help.xls using a VB Macro.concatenated result should be in W column.
from the sheet 1 i filter a column(Y2:AZ2) according to a date and remove the blanks from that column.
then i need to concatenate B and C columns according to the filtered column.that means i don't need to concatenate all the rows in B and C columns .i just only need to concatenate rows according to the filtered column.

as an example consider sheet 2 and 3
in sheet 2 i have filtered AG column and removed blanks.then i need to concatenate column B and C in to the column W,and other valued should not be concatenate
in sheet 3 i have filtered AL column and removed blanks.then i need to concatenate column B and C in to the column W,and other valued should not be concatenate

in this manner B and C should be concatenate in to W depending on the filtered Column

i tried it with recording a macro,but it didn't work and i don't know the reason for it.
could you please help me to find a macro which satisfy my requirement

thanks
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

rsami

New Member
Joined
Nov 17, 2011
Messages
17
i found this macro

Sub Concat()

Dim lRow As Long

lRow = Range("B" & Rows.Count).End(xlUp).Row

For i = 3 To lRow
Cells(i, 23) = Cells(i, 2) & "_" & Cells(i, 3)
Next i

End Sub

but it concatenate all the rows in column B and C.
i just need to concatenate only the rows which have values in filtered rows
any comment is highly appreciate
 

CodeNinja

Well-known Member
Joined
Feb 18, 2013
Messages
634
Test to see if the row is hidden... if it is not then concatenate.... Be sure to change the sheet# to suit your needs...

Code:
Sub Concat()

Dim lRow As Long

lRow = Range("B" & Rows.Count).End(xlUp).Row

For i = 3 To lRow
    If Sheet1.Rows(i).Hidden = False Then
        Cells(i, 23) = Cells(i, 2) & "_" & Cells(i, 3)
    End If
Next i

End Sub
 

par60056

Well-known Member
Joined
Jul 26, 2012
Messages
1,581
Office Version
  1. 2011
  2. 2010
Platform
  1. Windows
  2. MacOS
You don't need to specify the sheet if nothing else is specifying the sheet. Just test "Rows(i).Hidden"
 

rsami

New Member
Joined
Nov 17, 2011
Messages
17
You don't need to specify the sheet if nothing else is specifying the sheet. Just test "Rows(i).Hidden"

yes i tried your suggestion and it works with CodeNinja's code.
thanks very much for both of you
 

Watch MrExcel Video

Forum statistics

Threads
1,133,530
Messages
5,659,355
Members
418,498
Latest member
nattynat

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
Top