Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove blank rows without hiding them
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 -- peter.thompso ----------------------------------------------------------------------- peter.thompson's Profile: http://www.excelforum.com/member.php...fo&userid=2968 View this thread: http://www.excelforum.com/showthread.php?threadid=50084 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove blank rows without hiding them
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 "peter.thompson" wrote: 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), which results in many blank rows in the destination worksheet. At the moment I am hiding the blank rows with code, which now creates a problem for me in extracting data into a Word doc. Is there a way to populate the destination worksheet sheet such that the rows remain contiguous without any blank rows (i.e. no blank or hidden rows?). Any help much appreciated, please bear in mind I'm new to VBA! Cheers, Peter -- peter.thompson ------------------------------------------------------------------------ peter.thompson's Profile: http://www.excelforum.com/member.php...o&userid=29686 View this thread: http://www.excelforum.com/showthread...hreadid=500841 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hiding blank rows using a macro | Excel Discussion (Misc queries) | |||
Hiding Rows if Blank cell | Excel Worksheet Functions | |||
Hiding Rows if the linked rows are blank | Excel Discussion (Misc queries) | |||
Hiding blank rows | Excel Discussion (Misc queries) | |||
Hiding Rows with Zero or Blank Values | Excel Programming |