• 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.

add rows at end of selection [SOLVED?]

C

crombes

New Member
Hi VBA newbie in town

I was wondering if I can get a push in the right direction to solve a problem , i'm dealing with , I have to make a dynamic to do list , each topic on the to do list goes from colum B until colum K , all of the colums have merged cells from multiple rows as these do not change troughout the topic, except columns H & I as these have to be filled in and can change from contence , as a topic can take longer i would like to expand the "block" of the topic with 1 row a time so the merged cells and the columns H,I should have one row more , is this possible with VBA


cheers


link

https://www.rapidshare.com/#myrs_filemanager/file/12046
 
Hi, crombes!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about questions in general...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


And about this question in particular...


The link seems to be unreachable, would you please check it out disconnected from your RapidShare acount? Thank you.


Regards!
 
thanks for the introduction


I've putted in dropbox


https://www.dropbox.com/s/6cia50gtcjdmep9/2013%20TC%20action%20log_.xls


can you check it out ?


thx
 
Hi Crombes..


Its really hard to manage MERGE CELL in VBA.. just a suggestion, try to avoid it as much as possible.. use "Center Across Selection.


and if you still want to stick with Merge cell.. try below..

[pre]
Code: 
Sub InsertKeyPoint()
Set topadd = Cells(ActiveCell.Row, "B").MergeArea.Cells(1)
mercount = topadd.MergeArea.Rows.Count
deb = Range(Cells(topadd.Row, "H"), Cells(topadd.Row, "I")).Offset(mercount - 1)
Range(Cells(topadd.Row, "H"), Cells(topadd.Row, "I")).Offset(mercount - 1).ClearContents
Range(Cells(topadd.Row, "H"), Cells(topadd.Row, "I")).Offset(mercount - 1).EntireRow.Insert
Range(Cells(topadd.Row, "H"), Cells(topadd.Row, "I")).Offset(mercount - 1) = deb
End Sub
[/pre]

https://dl.dropboxusercontent.com/u/78831150/Excel/add-rows-at-end-of-MERGE-CELL%20%28crombes%29.xls


Regards,

Deb
 
Hi Deb


would it be possible to group each topic likewise the file?

(keeping in mind that topiclist is dynamic and can grow?)

sorry to bother again i have a lot to learn i guess


thanks!


https://www.dropbox.com/s/e2o419wjqka98kq/2013%20TC%20action%20log2.xls
 
Hi Crombes..


Sorry!.. I am not able to understand what do you want..


Please elaborate.. :(


Regards,

Deb
 
Hi Deb


Basically It comes down to that each topic should be grouped so that

you have a clear overview of the topics.


regards
 
Hi Crombes!


Try this..

[pre]
Code: 
Sub InsertKeyPoint()
Set topadd = Cells(ActiveCell.Row, "B").MergeArea.Cells(1)
Dim Response As VbMsgBoxResult
Response = MsgBox("Do you want to add a new line in topic: " & topadd & " ?", vbQuestion + vbYesNo, "Add Line")
If Response = vbNo Then Exit Sub
mercount = topadd.MergeArea.Rows.Count
With Range(Cells(topadd.Row, "H"), Cells(topadd.Row, "I"))
deb = .Offset(mercount - 1)
.Offset(mercount - 1).ClearContents
.Offset(mercount - 1).EntireRow.Insert
.Offset(mercount - 1) = deb
On Error Resume Next
Range(topadd.Address & ":B" & topadd.Row + mercount - 1).EntireRow.Ungroup
On Error GoTo 0
Range(topadd.Address & ":B" & topadd.Row + mercount - 1).EntireRow.Group
End With
End Sub
[/pre]

Regards,

Deb
 
Hi Deb


sorry to bother again , do you know how to show the first line instead of the last line in column I after you have grouped (because first line gives more details about issues that are tracked and last line is an update of the todo file)


thanks
 
Hi, crombes!

Just giving a look to what Debraj Roy posted, but it haven't changed the method for grouping/ungrouping. In both your original uploaded file and in his, grouping for topic 1 always display the 1st line, date 12/03/2012, being the last 19/03/2012.

Am I missing anything?

Regards!
 
Hi SirJB7,


if you add new topic for instance and then add a row, there is an autogroup of this topic, it then gives automatically last row of this topic instead of the first row


Hope you understand


Thanks
 
@Debraj Roy

Hi!

I'm trying to avoid getting messed with merged cells, so someone's knocking at your door...

Regards!
 
You must log in or register to reply here.
Back
Top