Empty Lines & Border Question (Excel or VBA solution)

[jamada]

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

 

[Datsmart]

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]

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]

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]

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]

ooppps , my mistake

change the "last" line like below;

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

....

 

[ageren]

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