Editing fields in bulk

B

bhola-bhala

I am trying to remove all extra rows between some text.. lets say:

XXXXX
-------
-------
YYYYY


I am trying to remove all ------ part like i want to keep row #1 & #3
but i want to remove row #2, ,how can i do that? And i am not talking
about 1 row, there are more than 10,000 records :)
 
D

Door

Your question is not easy to reply.

Do you want to know the vba code for this to happen on a Button or
Macro, or do you want to (manually) Sort the rows and bulk-delete all
----- lines, then re-sort into original sequence?
(ie, insert a column and sequence number = row - copy that column and
Paste Special = Values back over itsself, insert a further column and
test for XXXXX and YYYYY and note all others as 'del', sort over this
second column, delete all 'del' lines, re-sort over the first inserted
column and delete the two worker columns)

Are the ----- lines truly ----- or are they other (undefined) data.
What is the true definition of XXXXX lines and YYYYY lines?

Any further clues?
 
K

Kevin B

I've tested this code and it appeared to run ok, but save a copy of your
original worksheet before implenting the code just to be on the safe side.
It ain't pretty but it got the job done.

Sub RemoveRows()

Dim wb As Workbook
Dim ws As Worksheet
Dim lngRow As Long
Dim varVal As Variant

Set wb = ActiveWorkbook
Set ws = wb.Worksheets("Sheet1")

'Activate sheet 1 of the current workbook and
'select cell A1
ws.Activate
Range("A1").Select

'Suppress screen updating
Application.ScreenUpdating = False

'exit loop if row counter exceeds the maximum
'amount of rows
Do Until lngRow = 65537
'Get the value of the current cell
'and increment the row couner
varVal = ActiveCell.Value
lngRow = lngRow + 1
'Display what row is being processed on
'the status bar
Application.StatusBar = "Processing row " & _
Format$(CStr(lngRow), "#,###")
If varVal = "" Then
'if the current cells value is empty
'then delete the row, increment the
'row counter get the next value to
'evaluate and refresh the status bar
'message
Do Until varVal <> ""
If varVal = "" Then
Selection.EntireRow.Delete
varVal = ActiveCell.Value
lngRow = lngRow + 1
If lngRow = 65537 Then Exit Do
Application.StatusBar = _
"Processing row " & _
Format$(CStr(lngRow), "#,###")
End If
Loop
End If
'Once all blank rows have been removed between
'one data set and the next, select the next cell
'and loop
ActiveCell.Offset(1).Select
Loop

Set ws = Nothing
Set wb = Nothing
Application.StatusBar = False
Application.ScreenUpdating = True
Exit Sub

End Sub
 
Top