Remove Empty Cells........

Don't know how you feel about using a macro to do the job.

Perhaps this could be of use to you? To sort data run macro called "Move_Data" and see if the result on Sheet2 is what you are looking for.

Alf

Thanks Alf. Two problems. One, the first cell isn't always filled. The last one is if that helps. Two is it possible to have the info populate starting at cell C3.

Ok have fixed the range bit but not sure what you mean by

the first cell isn't always filled.

You also have merged cells in your spread sheet and the macro is not to happy about that I had to make some funny adjustments I'm not too sure about. Works in your uploaded file but check when you have more data in your sheet.

In sheet2 I've just copied your headings (merged cells) and macro will not remove them when doing a rerun.

Alf

what i mean is cell F3 and G3 are sometimes empty, but H3,I3,and so on do have data always. i noticed the =100 line in the macro, but sometimes it is not 100. sometimes the first 2 cells of the series are blank but the rest always have data. can it be changed to look for non blank cells starting in K3 and continue down, yet still record everything in the range F3:K3, and so on. See new example. I would like it to record the blank cells as well. Again thank you for the help Alf.

Your merged cells are really upsetting the applecart. I've tried using the other cells but no luck so far.

Managed to get macro to work checking column E and the value "Total" is ok? If not tell me and I'll have a go at it again.

It's slowly becoming a mater of pride for me to solve this problem.

Alf

I've noticed 1 problem(my fault). I need the data at Y3,Y14, and so on. Can the macro be set to give me all the data from cells A:Y when it finds "Total"?

Never mind. After a bit of trial and error i think i under stand the macro. Learning is doing. A smart man once told me that. Thank you so much for everything.

Thanks for feed back and glad you got it working.

Alf

Hey Alf is there a way to add another word to the macro and return both lines, one after the next?

Yes of course perhaps like this i.e. change line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

I used the Trim command as I found out that the label is not "Total" but "Total " and this makes a difference but the Trim command gets rid of the extra space at the end. You can see the extra space if you select cell E3 or E14 and press the F2 button on the keyboard.

The Offset command takes two values i.e. row offset and column offset. These values can be negative or positive depending on their position.
If for example the E column is the one you use for finding the value "Total" and the wish to copy the range from C column to K column the range to copy the size of the range is Range(cell.Offset(0, -2), cell(0, 6)) i.e. same row as your E value Offset(0, and 2 column to the left of the E column gives cell.offset(0, -2) and the second cell has a row offset of 0 and a column offset of 6 i.e. cell.Offset(0, 6)

So if you wish to copy all the way to the Y column you need to change the last cell offset from cell.Offset(0, 6) to cell.Offset(0, 20)

Alf

Ps If you are happy with my answers you could click on the small star (bottom left in my post) and give a rating to my help.

Question..... What would an exclusion variable look like? Lets say i wanted to skip the lines with "Total " that also have "Christian" in the Employee field. Is something like that possible?

One you could use assuming the E column is used for the "Total" would be:

Please Login or Register  to view this content.

where both the E and the C column is checked and both conditions must be fulfilled (E value "Total" and C value not "Christian") in order to proceed to the copy and past lines.

Alf

That's good to know. Thank you again.

Perfect! Excel Forum ROCKS!....... Thanks Alf.