Macro to pick already selected cells as a range and make a itemized list on new sheet.

[ash.mak]

Hi All,

I am trying to make a macro where I can simply select a range of cells (which can vary in size at times) and run my macro to convert that table as an itemized list on a new sheet. Example I have a table with 4 rows and 4 columns, giving me 16 cells of data. some cells have no or zero value. I want to show that table as a list like:

row1 column 1 value
row1 column 3 value
row 2 column 2 value
row 3 column 2 value

leaving out of the list rows and columns headers that had no value. You can imagine having over 200 columns and 200,000 rows in a table with many value fields empty. lol

My main worry is picking up the already selected range into the macro.

I have tables and ranges in different worksheets at different locations and different in shape also. If I can simply select a range of my choice and Run that macro. I can get results on a new sheet and save hours of tedious work

I've always been answered on this forum. May GOD bless you all.

THNX!!!

 

[JLGWhiz]

I am not really sure I understand what you mean, but see if this does what you want.

Code:
Sub pickStuffOut()
Dim sh As Worksheet, rng As Range, sh2 As Worksheet
Set sh = Sheets(1) 'Edit sheet name
Set sh2 = Sheets(2) 'Edit sheet name
Set rng = Selection
rw = 2
For Each c In rng
If c.Value <> "" Then
c.Copy sh2.Range("A" & rw)
rw = rw + 1
End If
Next
End Sub
Code:

One problem with this type of procedure is that the sheet which the selection is made on must be the active sheet when the procedure runs, else it fails. For instance if you make sheet two visible as the active sheet, the cell with the cursor on that sheet is now the selected range, and that will be what the procedure uses to do its work. But it will provide your list as it is, so long as you keep the sheet active on which you make the selection. Also, the procedure will need to be assigned to a button or a keyboard shortcut, unless you want to access the VBA editor to run it each time.

 

[ash.mak]

I am not really sure I understand what you mean, but see if this does what you want.

Code:
Sub pickStuffOut()
Dim sh As Worksheet, rng As Range, sh2 As Worksheet
Set sh = Sheets(1) 'Edit sheet name
Set sh2 = Sheets(2) 'Edit sheet name
Set rng = Selection
rw = 2
For Each c In rng
If c.Value <> "" Then
c.Copy sh2.Range("A" & rw)
rw = rw + 1
End If
Next
End Sub
Code:

One problem with this type of procedure is that the sheet which the selection is made on must be the active sheet when the procedure runs, else it fails. For instance if you make sheet two visible as the active sheet, the cell with the cursor on that sheet is now the selected range, and that will be what the procedure uses to do its work. But it will provide your list as it is, so long as you keep the sheet active on which you make the selection. Also, the procedure will need to be assigned to a button or a keyboard shortcut, unless you want to access the VBA editor to run it each time.

Thanks Whiz. I had it all figured out. My only worry is different range sizes, I'll use this macro on. So I want to be able to select a range Manually and run the macro. I want the macro to pick up the range I selected before running the macro. I hope it is more clearer now. Appreciate your help buddy.