• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro Ranges.. 2 questions

M

Mortadella

Member
1: I import a data sheet that I need to edit before posting it to another worksheet. When I import the data I insert a column which serves as a line counter. The imported data is usually skewed so I need to sort it out and this is where my problems start.


The range I usually have to sort contains data like the following:

Rank Col A Col B Col C Col D Col E Col F Col G---- to Col N

221 950.00 1220.00 22.95 201.25

222

223...

230 9755.00 197.25 258.95 896.22


My range finishes at Cell N800 but because the data changes in length each day I never know where it will start. My macro will get my cursor to the cell I want it to start at

but I can't figure out how to define the range from the cellpointer to N800 Excell always wants a cell address - so if you can help me figure that out that would really help.


2: As you can see from the layout I get a large number of cells full of numbers in Col C that need to be moved over into Col H and I need to put a 1 in column N on the same line so when I am done it looks like this:

Rank Col A Col B Col C Col D Col E Col F Col G ColH --------------- to Col N

221 950.00 1220.00 22.95 201.25 1

230 9755.00 197.25 258.95 896.22 1


I can sort this out quite easily by hand but the macro I have written (using a loop)takes a long time because I have to do the inserts line by line to insert the four columns I need to push my numbers over and add the 1 in Col N. I would love to be able to insert cells from the line this kind of data starts to the end of my file on line 800 but my range naming problem above has me scuppered.

I still have all the blank lines that I need to get rid of which I think the solution to part 1 will help me with.


If anyone can suggest a good reference book to learn basic "macroese" I would be very grateful.


Thanks!
 
I see my spacing didn't work the way I had hoped the 950.00 and the 9755.00 were supposed to show in col H and the 1 in Col N
 
Mortadella


Firstly; Welcome to the Chandoo.org Forums


Q1. Have you tried something like:

Range("a3").CurrentRegion

That will put a rectangular range that includes all cells with data contiguous with A3
 
When I do that I get a Compile Error: Invalid use of Property


When you say contiguous it sounds to me like you are trying to capture data around cell A3 - A3 is the cell in my range that will vary - I know it will go from wherever my cellpointer is to N800. Wouldn't a function like that stop once it hit an empty series of cells? My data on the range I am trying to define has seven cells of information seperated from any other cell that has the same amount of information by nine lines of no data at all. I usually sort the range so I can get all the data together and eliminate hundreds of blank lines.
 
Can you post a sample of your project? It's easier to understand and work with.

http://chandoo.org/forums/topic/posting-a-sample-workbook
 
I've never seen this before!! What a great idea and thanks for your help.


http://hotfile.com/dl/123609701/e52d8b8/Example_for_Pointy_Haired_Dilbert_Forum.xls.html


I have a number of notes in the file just in case they don't show up right off.
 
You must log in or register to reply here.
Back
Top