empty rows

[ahhhmed]

Hello every body:

Have yo got any idea of hoe to make excel 2007 automatically eliminate empty rows from a list of rows?

 

[asa]

Hi ahhhmed,


You should be able to auto-filter your list (cursor in list then DATA/Filter in ribbon, or Ctrl-Shift-L, or Alt-D,F,F), then filter for blanks, then highlight and delete rows, then unfilter.


Asa

 

[asa]

If Excel doesn't recognize your list as one contiguous list due to the blank rows, just highlight your whole list before initiating the auto-filter.

Asa

 

[ahhhmed]

Thanka asa,

Good idea; however I need this to be automatically done so I get the final result without highlighting the original list

 

[asa]

Hi ahhhmed,

I see you have a couple of other similar questions -

http://chandoo.org/forums/topic/grouping-items

http://chandoo.org/forums/topic/conditions


Are they related?

Do you still have the problem?


If you still need help, it would help me to understand your requirement better.


For example, in this list, when are the blanks generated and how?

Are the values stored directly in those cells or do some of them come from formulas that refers elsewhere?

When do you want the automatic removal of the blanks to occur?

Do you want the removal of blanks to happen "in-place"?


I developed a VBA routine that can delete empty rows on a worksheet, or on part of a worksheet, and it might be an appropriate solution for you. There are various ways to have it fire automatically according to different events or criteria, or it could be run when a button on the worksheet is clicked.


Asa

 

[magbo]

Hi!

How about sorting? It also makes a contiguous list. (if you do not need to respect order or your entries are already sorted)

 

[mkapashi]

Here is a quick way to delete all empty rows on a worksheet:

Below it is code to delete empty rows and columns on a worksheet.

Hope it helps.


Sub DeleteEmptyRowsOnly()


Application.ScreenUpdating = False


Dim r1 As Long, c1 As Integer, rr As Long, cc As Integer, ii As Integer


rr = Cells.Find("*", [a1], , , xlByRows, xlPrevious).Row

cc = Cells.Find("*", [a1], , , xlByColumns, xlPrevious).Column


r1 = Cells.Find("*", Cells(rr, cc), , , xlByRows, xlNext).Row


If r1 = 1 Then GoTo 200

Rows("1:" & r1 - 1).Delete


200:

On Error Resume Next


If ActiveSheet.UsedRange.Rows.Count <= 2 Then Exit Sub


rr = Cells.Find("*", [a1], , , xlByRows, xlPrevious).Row

cc = Cells.Find("*", [a1], , , xlByColumns, xlPrevious).Column


For ii = 1 To cc

Range([a1], Cells(rr, cc)).AutoFilter Field:=ii, Criteria1:="="

Next


Range([a2], Cells(rr, cc)).SpecialCells(xlCellTypeVisible).EntireRow.Delete


Range([a1], Cells(rr, cc)).AutoFilter


End Sub


----------------------------------------------------------------------------------------


Sub DeleteEmptyRowsColumns()


Application.ScreenUpdating = False


Dim r1 As Long, c1 As Integer, rr As Long, cc As Integer, ii As Integer

Dim col As String, c As Integer


rr = Cells.Find("*", [a1], , , xlByRows, xlPrevious).Row

cc = Cells.Find("*", [a1], , , xlByColumns, xlPrevious).Column


r1 = Cells.Find("*", Cells(rr, cc), , , xlByRows, xlNext).Row

c1 = Cells.Find("*", Cells(rr, cc), , , xlByColumns, xlNext).Column


If r1 = 1 Then GoTo 100

Rows("1:" & r1 - 1).Delete


100:

If c1 = 1 Then GoTo 200

col = ColLetter(c1 - 1)

Columns("A:" & col).Delete


200:

On Error Resume Next


If ActiveSheet.UsedRange.Rows.Count <= 2 Then GoTo 300


rr = Cells.Find("*", [a1], , , xlByRows, xlPrevious).Row

cc = Cells.Find("*", [a1], , , xlByColumns, xlPrevious).Column


For ii = 1 To cc

Range([a1], Cells(rr, cc)).AutoFilter Field:=ii, Criteria1:="="

Next


Range([a2], Cells(rr, cc)).SpecialCells(xlCellTypeVisible).EntireRow.Delete


Range([a1], Cells(rr, cc)).AutoFilter


300:

For c = cc To 1 Step -1

If WorksheetFunction.CountA(Columns(c)) = 0 Then Columns(c).Delete

Next


On Error GoTo 0


End Sub


Function ColLetter(ColNumber)

ColLetter = Replace(Split(Columns(ColNumber).Address, ":")(0), "$", "")

End Function

 

[rogerimee]

Hi,


Copy this code in your macro and run, it will automatically delete the row of empty cells in column A.


Sub DeleteBlankRows()

Columns("A:A").SpecialCells(xlCellTypeBlanks).Select

Selection.EntireRow.Delete

End Sub

 

[rogerimee]

But if you dont want to use macro just highlight the column A then follow the command below.


1. Ctrl G, Alt S, K and Enter

2. Ctrl -, R and Enter


I hope it will help you this method.