Remove blank rows without hiding them

[peter.thompson]

I have a column of data in a worksheet (50 rows A1:A50). At the moment
the data is hard coded i.e. A1=sheet2!ab1,B1=sheet2!ab2, etc.

Sometimes there is no data in source cells (e.g. ab15 ,ab19 etc), whic
results in many blank rows in the destination worksheet. At the momen
I am hiding the blank rows with code, which now creates a problem fo
me in extracting data into a Word doc.

Is there a way to populate the destination worksheet sheet such tha
the rows remain contiguous without any blank rows (i.e. no blank o
hidden rows?).

Any help much appreciated, please bear in mind I'm new to VBA!

Cheers,

Pete

 

[Guest]

What I have done in one instance of a similar situation (Not blank, but
undesired rows to be hidden and stay hidden.) Is to eventually create a new
worksheet that has all of the data that I actually want in a contiguous
fashion.

This is basically the code I use, to move all selected rows of data to a
different worksheet:



'SheetName is the name of the sheet to which data will be moved

Private Sub MoveData(SheetName As String)
'
' Move2Delete Macro
' Macro recorded 08/03/2004
'

' Get current active sheet and store sheet in variable.
' If current active sheet is Delete then don't do anything
' If keep sheet does not exist then create/rename it.
'

If ActiveSheet.Name = SheetName Then
Exit Sub
End If

If SheetExist(SheetName) = False Then
Call CreateNewSheet(SheetName)
End If

Dim Cell As Object
Dim Count As Long
Dim I As Integer
Dim DelRows() As Long
Dim CurrentSheet As Worksheet
Set CurrentSheet = ActiveSheet

'Get new row to enter data and select it. Finds the last row in which to
add new data on the destination sheet.
Count = GetNewRow(Sheets(SheetName))

With Selection 'Depends on the cells that are selected
Application.CutCopyMode = False
For Each Cell In Selection.Rows
If Cell.RowHeight <> 0 Then 'I.e., if the cell is not hidden
either by directly hiding it or performing a filter on the data
CurrentSheet.Rows(Cell.Row).Copy _
Destination:=Sheets(SheetName).Cells(Count, 1)
Count = Count + 1
End If
Next Cell

'If you do not want to delete the selected rows, then basically most
of the next sets of routines are not necessary.

Count = 0
For Each Cell In Selection.Rows
If Cell.RowHeight <> 0 Then
Count = Count + 1
End If
Next Cell

ReDim DelRows(Count - 1)
I = 0

For Each Cell In Selection.Rows
If Cell.RowHeight <> 0 Then
DelRows(I) = Cell.Row
I = I + 1
End If
Next Cell

'This next step deletes all of the selected items from the current
sheet, but deletes from the end to the beginning so that I don't have to keep
track of additional data.

For I = Count - 1 To 0 Step -1
CurrentSheet.Rows(DelRows(I)).Delete
Next I
'Select the new row stored above as active
'Range("A2").Select
'Return to previously active sheet.
CurrentSheet.Select

End With
End Sub

The GetNewRow function below is dependant on having a contiguous set of data
in your destination sheet starting at some given row. (Which is what you are
ultimately trying to get.)

Private Function GetNewRow(Optional ChosenSheet As Variant) As Long

Dim Count As Long

Count = 2 'Starting row

If IsMissing(ChosenSheet) = True Then
Set ChosenSheet = ActiveSheet
End If

With ChosenSheet
While .Cells(Count, 1) <> ""
Count = Count + 1
Wend
End With

GetNewRow = Count
End Function