Empty Lines & Border Question (Excel or VBA solution)

jamada

Active Member
Joined
Mar 23, 2006
Messages
323
2 questions if I may please

1) I have 1779 lines of information in columns "A" thru "D" however many rows have NO Information in them, is there a quick way 'Felete thiese unwanted emty rows?


2) If column A has text in it I would like to Place a border starting from that particular cell to the right to include columns A,B,C and D and to extend downwards to the next row in Column A that has text, BUT I do not want to include that next row, in other words the border would stop before the row in column A with text!

Hopefully that makes sense!!!


tks g
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
Question 1)
Easiest way to get rid of empty rows is to SORT your entire range of data.
The Empty rows will default to the bottom and can be ignored.

Question 2)
Can you explain that again???
 

ageren

Board Regular
Joined
Jul 30, 2007
Messages
60
answer 1 - copy the code below in your macro module...


sub delete_empty_lines

last = [a65500].end[3].row
range("a1").select
for i = 0 to last
if activecell.offset(i,0).value = 0 then
selection.entirerow.delete
else : activecell.offset(1,0).select
end if
next i


answer 2 - sorry, i couldn' t exactly understand your enquiry
 

jamada

Active Member
Joined
Mar 23, 2006
Messages
323
ageren

your macro almost works great, it gets hung up at the line indicating "last="
Errorr msg is compile errorr: Syntax error, the debugger highlights it in red?

I am not that familar with solvingingt these things

Datsmart

I tried the Sort but it does not seem to work for me, due to some cells not being the same size



As for question 2

If cells A1 had text in it then I would want a border around A1 to D1
AND to include all rows below row 1 UNLESS the following row had text in A2 and so on and so on...


tks gg
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
I tried the Sort but it does not seem to work for me, due to some cells not being the same size
Do you have some cells Merged? If so, that causes many issues with manipulating data, including using code like ageren suggested. Remove any merging and try again.
 

ageren

Board Regular
Joined
Jul 30, 2007
Messages
60
ooppps , my mistake :)

change the "last" line like below;

last = [a65500].end (3).row

....
 

ageren

Board Regular
Joined
Jul 30, 2007
Messages
60
i think you want to do something like that; (for your second questiion)


insert below codes right after your 1st code:

Range("A1:D" & last).End(xlDown).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
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub
 

Forum statistics

Threads
1,181,658
Messages
5,931,271
Members
436,786
Latest member
Deniel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top