LinkBack URL
About LinkBacks
hello all,
can anyone help me, i need a macro that will put in borders on a worksheet in all cells that are not empty, the next column of empty cells should also have borders.
Thanks in advance
hello all,
can anyone help me, i need a macro that will put in borders on a worksheet in all cells that are not empty, the next column of empty cells should also have borders.
Thanks in advance
for the whole worksheet?
If you think that my answer was helpful, please click on the "Add to this user's Reputation" button.
No, the cell range will be variable, so i think its some kind of FOR NEXT loop, the data will be in rows and the loop can end once it hits an empty cell.Originally Posted by Bishonen
Try this for the borders around non-empty cells part, it's not my code but I can't remember who wrote it.
You will need to give more details about the range for the empty column part as I am sure you don't mean literally the whole column
Please Login or Register to view this content.
If my solution worked (or not) please let me know. If your question is answered then please remember to mark it solved
Computers are like air conditioners. They work fine until you start opening windows. ~Author Unknown
The empty column will end where the rows end, for instance if the data is occupying cells A:1 to F20 then i would like to put borders in the column G1:G20, hope this makes sense.
Last edited by Cutter; 10-14-2012 at 01:03 PM. Reason: Removed whole post quote
It's more than a bit ugly but try the code below
Please Login or Register to view this content.
Last edited by WasWodge; 10-14-2012 at 08:20 AM. Reason: added LookIn:=xlValues
This isnt completely working, it does fine putting borders on data that has cells, but it needs to add another set of borders in the next column and the amount of bordered empty cells is equal to the amount of rows that contain data
Last edited by Cutter; 10-15-2012 at 01:53 PM. Reason: Removed whole post quote
Why don't you just use Conditional Formatting?
Select G2
Conditional Formatting > Formula is
Formula:
Please Login or Register to view this content.
Format: Border > Outline
Use the Format Painter to extend the format range as required.
If you need any more information, please feel free to ask.
However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....
Also
اس کی مدد کرتا ہے اگر
شکریہ کہنے کے لئے سٹار کلک کریں
If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.
in a macro?
Last edited by Cutter; 10-15-2012 at 01:54 PM. Reason: Removed whole post quote
If you don't go with the suggestion by Marcol can you attach a copy of your workbook with any sensitive data replaced with a single letter so l can test it as when I ran the macro on my data it added the borders to the first empty column and I will look at it later tonight.
Why use a macro? ...
Excel will automatically add/remove borders as your data changes if you use C/F.
ok ive tried to upload a file to show what i am trying to do
The data itself will be will be extracted from a csv file, the amount of columns will always be the same, but the amount of rows will vary.
So, i now know how to border each cell that has data, i now want to border each cell in column G, the amount of cells bordered in column G is equal to the amount of rows that have data on that worksheet.
Thanks
Your file does not appear on my phone as uploaded.
Click Go Advanced,then you will see an icon that looks like a paperclip. Click that and you can upload a file from there.
Then I will try and have a look tonight
Because you are uploading from a CSV file you will probably find that the cells are not truly blank but we will see
i hope this has worked!
ok, as i previously said...
The data itself will be will be extracted from a csv file, the amount of columns will always be the same, but the amount of rows will vary.
So, i now know how to border each cell that has data, i now want to border each cell in column G, the amount of cells bordered in column G is equal to the amount of rows that have data on that worksheet, which in this example will be 15.
thanks
Is the file you posted one you created manually or from your CSV file?
The reason I ask is when I run the macro I posted it puts borders around the filled cells and then puts borders around the cells in the first blank column (this is column E and not G in your sample file).
If you created it manually then I won't be able to tell if there are any non-printing characters causing you the trouble.
If you just want it to border Column G to the last row then change the linetoPlease Login or Register to view this content.but like i said the first blank column in your sample is Column E (which you put borders around even though it doesn't hold any data) and not not column GPlease Login or Register to view this content.
If your actual data does go to column F and the code I posted is not putting borders in Column G then either you will need to post an actual copy of your actual sheet (you can type whatever you like in columns A to F to replace any confidential data as I am only interested in what is in the cells from Column G onwards) or run the macro below. It will return you a column number i.e. it should give 6 for column F. If it doesn't give you the number 6 then post pack the number it gives you.
Please Login or Register to view this content.
Last edited by WasWodge; 10-18-2012 at 03:48 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks