PDA

View Full Version : Dynamic selection of columns in Excel



sisterProg
11-26-2012, 01:48 PM
Hello, I am new to VBA and need your help.
I want to create a macro to do this:
When the user selects some columns, the content of those columns is copied to a new sheet. Will be copied only the rows marked with "yes" in a column which will not be copied.

How do I use the user's selection in VBA? May be a range of columns which is not continue and it's a dynamic number of columns (user-defined).

Thank you in advance, VBA -Community .

Bob Phillips
11-26-2012, 03:50 PM
Sub CopyData()
Const ID_COLUMN As String = "B"
Dim ws As Worksheet
Dim source As Range
Dim lastrow As Long
Dim i As Long

Set ws = ActiveSheet
Set source = Selection
Worksheets.Add
With ActiveSheet

lastrow = ws.Cells(ws.Rows.Count, ID_COLUMN).End(xlUp).Row
source.Copy .Range("A1")
For i = 1 To lastrow

If ws.Cells(i, ID_COLUMN).Value <> "yes" Then

.Cells(i, "A").Resize(, source.Columns.Count).ClearContents
End If
Next i
End With
End Sub

sisterProg
11-27-2012, 05:26 AM
Thanks, xld. I am very happy you answered so quickly.

I am using M Office 2010 and perharps that's why I've got some errors.
1) Type mismatch in
lastrow = ws.Cells(ws.Rows.Count, ID_COLUMN).End(xlUp).Rows.Count
I replaced it with
lastrow = ws.UsedRange.Rows.Count
2) The object is missing in the line
source.Copy.Range ("A1")
If I just use it with . (dot) in front, I get also error.

Please help.

thanks a lot!

Bob Phillips
11-27-2012, 07:06 AM
I have 2010 as well, and it all worked in my tests, so maybe post a workbook.

sisterProg
11-27-2012, 07:32 AM
I have a huge workbook!

Bob Phillips
11-27-2012, 09:09 AM
I have just looked again at the code you posted. That is not the code I gave you so I suggest you re-check what you did.

sisterProg
11-27-2012, 01:26 PM
Hi,

in this line
lastrow = ws.Cells(ws.Rows.Count, ID_COLUMN).End(xlUp).Row
I've got "type mismatch", that"s why I change it a bit.

So, the problem is still opened...

Bob Phillips
11-27-2012, 04:19 PM
First time you said you got a type mismatch with

lastrow = ws.Cells(ws.Rows.Count, ID_COLUMN).End(xlUp).Rows.Count

which is not what I gave, then you say it is with

lastrow = ws.Cells(ws.Rows.Count, ID_COLUMN).End(xlUp).Row

Sorry, but I cannot trust what you have done, so there is no point trying anything unless you post the workbook with what code you have added so we can see what has been done.

sisterProg
11-28-2012, 03:05 AM
If you don't trust me, I trust you and this community.

Let's see. I have a more than 10000 lines in my original worksheet. I created a model, and I attach it here. it seems the deletion of cells doesn't work.

I must confess I would prefer to copy in the new sheet only the rows with a certain value in them, not to copy everything and delete after. That's because of the huge amount of input data which may not be all present in the resulting data.

Thank you in advance. You're great!

sisterProg
11-28-2012, 01:53 PM
Any help, please?