Remove gridlines from empty cells 1

[Enkrypted]

I have an excel sheet and I'm trying to find a way to remove gridlines from a set of cells on a worksheet. The only thing is, I need to only delete the gridlines of empty cells. I'm not sure how to go about doing that, so any help would be appreciated. TIA!

Enkrypted
A+

 

[SkipVought]

Hi,

are you sure there are gridlines?

What about the "gridlines" from adjacent cells?

You might want to add gridlines to ALL cells using Format > Cells > Borders, and then conditionally "remove" then using Format > Conditional Formatting.

Or if there are just the default "gridlines" use CF to change the interior color to WHITE.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[buitenhek]

try conditional format under format header:

if cell value ="" then set border to blank in format button




Regards
Peter Buitenhek
ProfitDeveloper.com

"Never settle for a job well done...always look for cost cutting measures

 

[Enkrypted]

I created a macro that will remove the gridlines, however I don't know how to add a check in there to skip any cells that have text in them:

Sub Macro2()

    Range("A50:J52").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
End Sub

Enkrypted
A+

 

[SkipVought]

I don't know how to add a check in there to skip any cells that have text in them:

Why would you write code instead of using native Excel features?

Your code acts on the entire selected range. Your code would have to loop thru each cell in the range to test to the existance of data or not.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

You could select your range ([Ctrl]+[A]) then go to Edit > Goto > Special > Blanks then select no borders.

But that will remove the edge borders or adjacent cells which contain data.

I have a feeling your making this WAAAAAY more complicated that it should be.

Are the 'empty cells' in the middle of the data set, or at the bottom (or right) of the remaining data?

Heck, I'm still not even sure if you mean borders or gridlines. Gridlines are the faint lines you see between cells as soon as you open a new Excel workbook. (which, btw, can be removed in Tools > Options).

Please provide some sample data of what you start with, what you're deleting, and what you want to end up with.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Enkrypted]

It is borders, not gridline, my bad. What I am trying to accomplish is we have a range of cells on an excel sheet that will get data put in them. This data will vary for each use of the file. We would like to remove the borders of the empty cells to clean it up a little bit. The example is shown below:

So basically, the person using the Excel file will fill in the information they want in that section and whatever is not being used, they can push a macro button and the border will be removed. I hope this helps clarify things

Enkrypted
A+

 

[SkipVought]

Conditional Formatting!!!!!

Easy Peasy!!!

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

Seriously - no macro. Conditional Formatting all the way.

There's not even a button to push. The user just enters data, and borders appear. That's it.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Enkrypted]

You'll have to forgive me, I don't use Excel alot and am trying to figure it out. I tried the conditional format as mentioned above:

Cell Value Is equal to ""

and then I set the format to no border, but it didn't do anything.

I know this is probably easier than I am making it out to be, but just trying to figure it out.

Enkrypted
A+

 

[SkipVought]

Select your area to configure & Select Borders on EVERY CELL.

Format > Conditional Formatting...

Formula Is: select the TOP LEFT CELL in your range and make RELATIVE:RELAIVE

Format > Border Tab > Select NONE

FINISH

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

Personally I'd think of it the other way, adding borders where cell are not blank. But it will work either way.

The way you're doing it, though, requires that you add borders to whatever cells might ever be populated. The CF will effectively remove those borders from blank cells.

If you're really dealing with something like 50 rows of data, you can disregard this next bit, but if you need to account for the possibility of lots of rows (hundreds or thousands), you'll actually use less overhead by adding a conditional formatting to entire columns rather than a range like A1:J10000.

So, to take it from the top, from the other point-of-view:
[ul][li]Select columns A:J[/li]
[ul][li]Make sure that A1 is the active cell[/li][/ul]
[li]Go to Format > Conditional Formatting[/li]
[li]Change the first box to Formula is[/li]
[li]Change the second box to [COLOR=blue white]=$A1<>""[/color] or [COLOR=blue white]=Not(IsBlank($A1))[/color] (if you might have formulas returning "" in column A)[/li]
[li]Press the Format button[/li]
[li]Select the type of borders you'd like[/li]
[/ul]
Now whenever you type something into column A, borders will appear for all cells in columns A:J for that row.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Enkrypted]

Sorry for the late response. I went with Anotherhiggins suggestion and it worked perfect. Thanks guys!

Enkrypted
A+