Concatenate only where there is data

**tiger10012** · 10-05-2011, 12:29 PM

Hi All,
I created a macro (mostly by copying/pasting macros from other posts and tweeking) that:
1. Inserts a column C
2. Concatenates A+B into the new column C,
3. Pastes the values
4. Shades every other block of new data within column C, and
5. Deletes the column C that was created.

The issue that I am having is the fact I don't know how to limit all of the above to only rows that have data in column B. In other words, the macro is doing this for 65,000 lines! How could I limit the above to only rows with data in column B?

Please Login or Register  to view this content.

As always, thank you kindly for any assistance you are able to offer.
Bryan

**Kenneth Hobson** · 10-05-2011, 12:48 PM

Please Login or Register  to view this content.

**tiger10012** · 10-05-2011, 01:11 PM

Thank you very much for the information Kenneth. When I ran the updated macro, I received an "Overflow" message. There were 2 places where I incorporated the "& lrColB". Did I mess up my updates? Here is the updated code:

Please Login or Register  to view this content.

**Kenneth Hobson** · 10-05-2011, 01:26 PM

Sorry, forgot the rows part.

Please Login or Register  to view this content.

I don't use the SpecialCells method because it only updates after a save. Use a method like mine.

**tiger10012** · 10-05-2011, 01:50 PM

I get a "400" error code when I run the macro now. I have trimmed the code to this, and still get the "400" error code:

Please Login or Register  to view this content.

**Kenneth Hobson** · 10-05-2011, 02:07 PM

Diet coke withdrawal I guess:

Please Login or Register  to view this content.

**tiger10012** · 10-05-2011, 02:38 PM

Still getting the "400" error message

**Whizbang** · 10-05-2011, 02:42 PM

I like to use this method:

Please Login or Register  to view this content.

Or

Please Login or Register  to view this content.

**Whizbang** · 10-05-2011, 02:52 PM

Originally Posted by Kenneth Hobson

I don't use the SpecialCells method because it only updates after a save. Use a method like mine.

Oops. Didn't read fully before posting. I didn't realize that specialcells use the last saved values. Thanks.

**Kenneth Hobson** · 10-05-2011, 03:37 PM

Use xLup rather than xOneUp.

**tiger10012** · 10-05-2011, 03:55 PM

There were 2 things going on:
1. My blindness, and
2. The error code 400 will appear if there is no data on the spreadsheet. When I was troubleshooting, I kept moving components of code to new spreadsheets. Didn't think to take the data as well.

Thank you very much everyone!

Concatenate only where there is data

LinkBack

Thread Tools

Rate This Thread

Display

Concatenate only where there is data

Re: Concatenate only where there is data

Re: Concatenate only where there is data

Re: Concatenate only where there is data

Re: Concatenate only where there is data

Re: Concatenate only where there is data

Re: Concatenate only where there is data

Re: Concatenate only where there is data

Re: Concatenate only where there is data

Re: Concatenate only where there is data

Re: Concatenate only where there is data

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions