Find a text and select the entire used column

Jeyaraj2021

New Member
Joined
May 13, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi,

I need to find a text (ex: 90day) from header and based on this search i need to select the until the last used column(not the entire column) and then need to copy and paste.

But this 90day text can appear in any of the column, so the vba to find the specific text and select same last used column

Hope am clear on my questions
Thanks,
Jeyaraj M
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,966
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Welcome To MrExcel Message Board.
1. Are your header row is Row 1?
2. if your lastrow at column for e.g. is 25 then Copy row 1 to 25 at that column?
3. where you want paste?
4. Are you familiar with VBA?
 

Jeyaraj2021

New Member
Joined
May 13, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
1)Yes row header in Row1 (the text 90day may appear in any column as it is dynamic) so cant able to say the specific column. it has to find the text 90day and then select that particular column (until last used)
2)yes to select only the last used column
3) eg copy from sheet 1 to sheet 2
4) yes
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,966
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Try this:
VBA Code:
Sub FindColumn()
Dim i As Long, j As Long, Lr As Long, LC As Long
LC = Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column
For j = 1 To LC
If Sheets("Sheet1").Cells(1, j).Value = "90day" Then
Lr = Sheets("Sheet1").Cells(Rows.Count, j).End(xlUp).Row
Range(Sheets("Sheet1").Cells(1, j), Sheets("Sheet1").Cells(Lr, j)).Copy Sheets("Sheet2").Range("A1")
End If
Next j
End Sub
 
Solution

Jeyaraj2021

New Member
Joined
May 13, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Try this:
VBA Code:
Sub FindColumn()
Dim i As Long, j As Long, Lr As Long, LC As Long
LC = Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column
For j = 1 To LC
If Sheets("Sheet1").Cells(1, j).Value = "90day" Then
Lr = Sheets("Sheet1").Cells(Rows.Count, j).End(xlUp).Row
Range(Sheets("Sheet1").Cells(1, j), Sheets("Sheet1").Cells(Lr, j)).Copy Sheets("Sheet2").Range("A1")
End If
Next j
End Sub
This worked for me , made some tweak based on my report. Thanks for your support
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,966
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
You're Welcome & Thanks for Feedback.
 

Forum statistics

Threads
1,136,274
Messages
5,674,772
Members
419,525
Latest member
helensesc

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