Move all rows 1 down but skip row 26 & Public Function IF problem

Hey guys, its me again :p

I've got a pretty nice Worklist setup in Excel that enables you to organize all the work you need to do in a month. However i end up with little holes in the list whenever i finish a task because what my macroes does is whenever a job is marked as done on the list it moves that job to sheet2, which contains completed jobs. I'd like to have a macro that shifts all the rows 1 down whenever a job is completed(ill just call it from the job complete macro) but it needs to jump over(skip) row 26 because that row contains some images that my macros use. Altso i would like to, if possible avoid inserting any rows or cells or hiding them because that will totally mess up my macros :p I know i know, im not dynamic enough.


I've included a sample of the worksheet so you can see what i want.

By the way, I've altso got some trouble with a public function.

Please Login or Register  to view this content.

I'm trying to have this code insert Timestamp whenever a cell in side the Ranges is clicked, so far it does work but the timestamp changes when its clicked again, so i tried entering If Target = 0 to force it to only do it when the cell is empty, but it runs on all the cells now Anyone know a quick fix for this? =)

Thanks in advance,

George.

Hi George

For your second question try:

Please Login or Register  to view this content.

Thanks! That worked well Just 1 quick note, is there any easy command to have the range ignore row 26? or do i have to define each cell individually then?

Cheers

upon further testing i realized that this new code is interfering with me existing code,

whenever i try to run this:

Please Login or Register  to view this content.

I get the error message: run time error 13 type mismatch

and when i click debug i'm pointed to:

Please Login or Register  to view this content.

The part in bold is the part that is yellowed out. I'm assuming something here is interfering with my code running properly.

Ok, so the part that was supposed to be bold is

Please Login or Register  to view this content.

File Type: xls Book1.xls (165.5 KB, 2 views)

I uploaded this the same time i made the first post..

This contains the relevant data, or a sample of it anyway.
It's the first sheet i need shifted down, i've already sorted sheet2 with a simple insert.shift.xldown

But the 1st sheet isnt so simple since it has a huge gap in Row 26 and i don't know how jump over it without using an offset and that would mean offsetting each and every row.. Would be nice if someone knew an easy way

Awesome! Thanks heaps for that answer :-)
Wish there was a feature for this in Excel where you could ignore a certain row from Shift:=xlDown. Oh well, this code you wrote is certainly very flexible and it can probably be implemented into any existing sheet again, Thank you for spending your time to help me. Rep has been added

Hate to ask but does anyone know the answer to my second question too? My workbook won't run without solving that particular issue.

To recap i have the code i want but it seems to be working outside its range from what i can understand, this is the code:

Please Login or Register  to view this content.

Whenever i click a button i made that runs this macro:

Please Login or Register  to view this content.

The RemovePic2 macro code is:

Please Login or Register  to view this content.

I can only assume something here is messing with the Timestamp code.

When i run the code i just pasted i get: Run time error 13 type mismatch and when i click debug i'm pointed to:

Please Login or Register  to view this content.

Inside the Timestamp function.

Anyone got any ideas?

By the way, i get the same error whenever i select a bunch of cells by dragging around them, i tried changing Target to Trgt by dimming it as range but then it just gives me a compile error.

edit: altso tried changing Target = 0 to Target = "" but it didnt affect it in any way.

Awesome! When you put it that was using Selection does seem pretty silly :D I didn't know there was a Double Click event to use, i'll definately use that

Thanks a bunch for your time

Originally Posted by mikerickson

You could put this in the sheet's code module. Then if a cell (in a discreet location) had the formula =ROWS(1:65536), the Calculate event would be triggered by the inserting or deleting of a cell.

Please Login or Register  to view this content.

In the attached Row 5 is the stationary row.
The action is restricted to columns 1-50 of that row, but the constants can be altered to meet your situation.
For example, you want stationaryRow to be 25

Edit: One line should be changed.

I am trying to get this to work so far it does not do what i want.. I am posting my entire workbook since it does not contain any sensitive information.

Whenever i insert a row stationary row 26 becomes row 27 and whenever my macro does selection.clearcontent and a gap appears between lines(you'll see what i mean in the worksheet) it does not shift them in the proper order..

...Once again i failed to upload a file attachment to this post and had to resort to alternative file hosting, hopefully this will be ok with whoever writes an answer.. I would use excelforum if i could but it just gives me errors.. and no, the xls is not above 1mb :P

Anyway, here's the workbook just type in the captcha and hit Free Download..

http://ulozto.net/3797153/tiltakslistev3.xls

Thanks!