Applying Macros to Selections only.

**Elinah** · 02-13-2014, 12:28 PM

Hi,

I'm fairly new (and self-taught -.-) to VB programming, and had previously only used Macros for "easy" stuff like doing the same Math function over and over again, making things colourful etc.

I have massive amounts of Data coming in from a scientific machine now, which I then transfer to txt files, which I read into excel (which works fine).
I've now written a macro that neatens it up, and deletes columns/rows I don't need.
However, I can only write it to work for one batch of data at a time, since the data sets are slightly different (I have macros for the different kinds). As the macros however involve cutting cells/columns, I can't currently read all the data sets in at once, but need to copy them in one by one, the copy the resultant cells out into another spreadsheet, and so on, which is much better than the practice people here have been applying before (copying every data by hand typing them into the appropriate cells).
Is there a way to only apply a Macro to a given selected range of cells? That way I could select the data batches one by one and apply the macros, after inserting all of them in one go to excel.

Thanks for your help!
Eli

TL;DR: Is there a way to get Macros to only work in a selected area of cells?

**Olly** · 02-13-2014, 12:43 PM

Yes, of course you can limit code to a selection, or specific range. - it will be easier to advise the best approach if you attach an example workbook, showing how your data is structured and how you identify each separate range to work with.

**Elinah** · 02-13-2014, 01:10 PM

Here's a representation of what I'm doing - the colourful bit is what it looks like when the macros are done, and the rest is what my input would normally look like.
I've named things random letters and numbers, because I didn't want my actual data/topic on the internet at this stage, hope that's okay. As you can see, it cuts columns out, and I was planning to just manually select a dataset at a time, then run my macros on the selections one after the other, so at the end I would end up with everything cut to what I need and color-coded. Is this possible?

Thanks for the help, EliElinah1.xlsm

**Arkadi** · 02-13-2014, 01:25 PM

Hi Elinah,

I am fairly new and self taught as well

Fun stuff eh? I'm afraid I don't fully understand what your modifications of selected cells are, but I do know that if it is a cell by cell process then you could simply do an action for each cell in a range one by one...:

Sub test()
For Each cell In Selection
    cell.Value = 1 '(or whatever you want to do with them... just random example)
Next cell
End Sub

alternatively, if you want to work with that range you can dim a variable as range, and then "set variable = selection" with makes the range the selected cells.

hope that helps a little,

Arkadi

**Elinah** · 02-13-2014, 01:32 PM

Thanks, Arkadi!

unfortunately, I am removing entire columns at the moment, which would then mess up the dataset below it. As the datasets are slightly different from each other however (about 3-4 "general" options), I'd like to have it as a seperate process for each dataset, so I can select individually which macro to apply.

Eli

**Arkadi** · 02-13-2014, 02:02 PM

Eli,

So are you trying to figure out how to remove a column, but only the rows in the currently selected area? Do you select the entire range that the macro modifies before starting your macro?

If it is based on an area you manually highlight before processing, then this might be what you are looking for? If the range is pre-defined, just replace "Set rng = Selection" with "Rng = the range you want"

Sub delete_column_in_row_range()
Dim rng As Range
Dim frow, lrow As Integer 'first and last rows of your selected range
Set rng = Selection ' this takes selected area to figure out the rows
frow = rng.Cells(1, 1).Row 'first row in selected range
lrow = rng.Cells.Rows.Count + frow - 1 'last row in selected range (row# on the sheet)
Range("A" & frow, "A" & lrow).Select 'selects column A within your original selection
Selection.Delete shift:=xlToLeft 'removes the selected cells and shifts the rest to the left
End Sub

Applying Macros to Selections only.

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Applying Macros to Selections only.

Re: Applying Macros to Selections only.

Re: Applying Macros to Selections only.

Re: Applying Macros to Selections only.

Re: Applying Macros to Selections only.

Re: Applying Macros to Selections only.

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Worksheet Selections in Macros

[SOLVED] Worksheet Selections in Macros

[SOLVED] Worksheet Selections in Macros

[SOLVED] Worksheet Selections in Macros

[SOLVED] Worksheet Selections in Macros

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions