Using Find/Replace in Visual basic to adapt the code
 

Is there are way of using either Find/Replace or some othe procedure within
Visual Basic to make global changes to a pattern of cells to be selected.

For example, if I wanted to select and delete 2 rows in a block, using say

Range ("A3:H3").Select and Range ("A66:H66").Select

then wanted to create another procedure which would select:

Range ("A3:H4").Select and Range ("A66:H67").Select etc etc

How would I do this?

Using Find/Replace in Visual basic to adapt the code
 

If I understand you correct
You can use this

Range("A3:H3,A66:H66").Select

But you don't have to select

Range("A3:H3,A66:H66").EntireRow.Delete


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Zakynthos" wrote in message ...


Is there are way of using either Find/Replace or some othe procedure within
Visual Basic to make global changes to a pattern of cells to be selected.

For example, if I wanted to select and delete 2 rows in a block, using say

Range ("A3:H3").Select and Range ("A66:H66").Select

then wanted to create another procedure which would select:

Range ("A3:H4").Select and Range ("A66:H67").Select etc etc

How would I do this?

Using Find/Replace in Visual basic to adapt the code
 

'Ron,

Sorry, I didn't explain myself very well!!!!

I have a lot of data to process and 'recording' each macro I need to extract
data will be very time consuming, so I'm looking for as many shortcuts as I
can find!

Given that I have recorded a macro to delete ONE whole row (say, columns
a:h) in every 60 or so rows and I now want a macro (without having to go
through the long process of 'recording' it!) to delete TWO consecutive rows
in every 60 rows, how can I get a 'quick fix' (FInd/Replace???, but how???)
to replace ALL instances of a1:h1, a66-h66 etc with a1:h2, a66-h67 etc etc



"Ron de Bruin" wrote:

If I understand you correct
You can use this

Range("A3:H3,A66:H66").Select

But you don't have to select

Range("A3:H3,A66:H66").EntireRow.Delete


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Zakynthos" wrote in message ...


Is there are way of using either Find/Replace or some othe procedure within
Visual Basic to make global changes to a pattern of cells to be selected.

For example, if I wanted to select and delete 2 rows in a block, using say

Range ("A3:H3").Select and Range ("A66:H66").Select

then wanted to create another procedure which would select:

Range ("A3:H4").Select and Range ("A66:H67").Select etc etc

How would I do this?

Using Find/Replace in Visual basic to adapt the code
 

Zakynthos,

Use find/replace to replace

").Select

with

").Resize(2).Select

That will change all your select statements to select 2 rows.

HTH,
Bernie
MS Excel MVP


"Zakynthos" wrote in message
...
'Ron,

Sorry, I didn't explain myself very well!!!!

I have a lot of data to process and 'recording' each macro I need to extract
data will be very time consuming, so I'm looking for as many shortcuts as I
can find!

Given that I have recorded a macro to delete ONE whole row (say, columns
a:h) in every 60 or so rows and I now want a macro (without having to go
through the long process of 'recording' it!) to delete TWO consecutive rows
in every 60 rows, how can I get a 'quick fix' (FInd/Replace???, but how???)
to replace ALL instances of a1:h1, a66-h66 etc with a1:h2, a66-h67 etc etc



"Ron de Bruin" wrote:

If I understand you correct
You can use this

Range("A3:H3,A66:H66").Select

But you don't have to select

Range("A3:H3,A66:H66").EntireRow.Delete


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Zakynthos" wrote in message
...


Is there are way of using either Find/Replace or some othe procedure within
Visual Basic to make global changes to a pattern of cells to be selected.

For example, if I wanted to select and delete 2 rows in a block, using say

Range ("A3:H3").Select and Range ("A66:H66").Select

then wanted to create another procedure which would select:

Range ("A3:H4").Select and Range ("A66:H67").Select etc etc

How would I do this?

Using Find/Replace in Visual basic to adapt the code
 

This example select the cells for testing
Use this line if it is working correct
'If Not rng Is Nothing Then rng.EntireRow.Delete

Sub UnionExample()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
Dim rng As Range

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = 10000
For Lrow = StartRow To EndRow Step 60
If rng Is Nothing Then
Set rng = .Cells(Lrow, "A").Resize(2, 1)
Else
Set rng = Application.Union(rng, .Cells(Lrow, "A").Resize(2, 1))
End If
Next
End With

'Delete all rows in one time
If Not rng Is Nothing Then rng.EntireRow.Select
'If Not rng Is Nothing Then rng.EntireRow.Delete

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Zakynthos" wrote in message ...
'Ron,

Sorry, I didn't explain myself very well!!!!

I have a lot of data to process and 'recording' each macro I need to extract
data will be very time consuming, so I'm looking for as many shortcuts as I
can find!

Given that I have recorded a macro to delete ONE whole row (say, columns
a:h) in every 60 or so rows and I now want a macro (without having to go
through the long process of 'recording' it!) to delete TWO consecutive rows
in every 60 rows, how can I get a 'quick fix' (FInd/Replace???, but how???)
to replace ALL instances of a1:h1, a66-h66 etc with a1:h2, a66-h67 etc etc



"Ron de Bruin" wrote:

If I understand you correct
You can use this

Range("A3:H3,A66:H66").Select

But you don't have to select

Range("A3:H3,A66:H66").EntireRow.Delete


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Zakynthos" wrote in message ...


Is there are way of using either Find/Replace or some othe procedure within
Visual Basic to make global changes to a pattern of cells to be selected.

For example, if I wanted to select and delete 2 rows in a block, using say

Range ("A3:H3").Select and Range ("A66:H66").Select

then wanted to create another procedure which would select:

Range ("A3:H4").Select and Range ("A66:H67").Select etc etc

How would I do this?

Using Find/Replace in Visual basic to adapt the code
 

Bernie,

Many thanks!! You've solved my problem.

Regards

Tony

"Bernie Deitrick" wrote:

Zakynthos,

Use find/replace to replace

").Select

with

").Resize(2).Select

That will change all your select statements to select 2 rows.

HTH,
Bernie
MS Excel MVP


"Zakynthos" wrote in message
...
'Ron,

Sorry, I didn't explain myself very well!!!!

I have a lot of data to process and 'recording' each macro I need to extract
data will be very time consuming, so I'm looking for as many shortcuts as I
can find!

Given that I have recorded a macro to delete ONE whole row (say, columns
a:h) in every 60 or so rows and I now want a macro (without having to go
through the long process of 'recording' it!) to delete TWO consecutive rows
in every 60 rows, how can I get a 'quick fix' (FInd/Replace???, but how???)
to replace ALL instances of a1:h1, a66-h66 etc with a1:h2, a66-h67 etc etc



"Ron de Bruin" wrote:

If I understand you correct
You can use this

Range("A3:H3,A66:H66").Select

But you don't have to select

Range("A3:H3,A66:H66").EntireRow.Delete


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Zakynthos" wrote in message
...


Is there are way of using either Find/Replace or some othe procedure within
Visual Basic to make global changes to a pattern of cells to be selected.

For example, if I wanted to select and delete 2 rows in a block, using say

Range ("A3:H3").Select and Range ("A66:H66").Select

then wanted to create another procedure which would select:

Range ("A3:H4").Select and Range ("A66:H67").Select etc etc

How would I do this?

Using Find/Replace in Visual basic to adapt the code
 

Ron,

Many thanks!!! Not sure I understand it all, but I'll certainly give it a go!

Best wishes

Tony

"Ron de Bruin" wrote:

This example select the cells for testing
Use this line if it is working correct
'If Not rng Is Nothing Then rng.EntireRow.Delete

Sub UnionExample()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
Dim rng As Range

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = 10000
For Lrow = StartRow To EndRow Step 60
If rng Is Nothing Then
Set rng = .Cells(Lrow, "A").Resize(2, 1)
Else
Set rng = Application.Union(rng, .Cells(Lrow, "A").Resize(2, 1))
End If
Next
End With

'Delete all rows in one time
If Not rng Is Nothing Then rng.EntireRow.Select
'If Not rng Is Nothing Then rng.EntireRow.Delete

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Zakynthos" wrote in message ...
'Ron,

Sorry, I didn't explain myself very well!!!!

I have a lot of data to process and 'recording' each macro I need to extract
data will be very time consuming, so I'm looking for as many shortcuts as I
can find!

Given that I have recorded a macro to delete ONE whole row (say, columns
a:h) in every 60 or so rows and I now want a macro (without having to go
through the long process of 'recording' it!) to delete TWO consecutive rows
in every 60 rows, how can I get a 'quick fix' (FInd/Replace???, but how???)
to replace ALL instances of a1:h1, a66-h66 etc with a1:h2, a66-h67 etc etc



"Ron de Bruin" wrote:

If I understand you correct
You can use this

Range("A3:H3,A66:H66").Select

But you don't have to select

Range("A3:H3,A66:H66").EntireRow.Delete


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Zakynthos" wrote in message ...


Is there are way of using either Find/Replace or some othe procedure within
Visual Basic to make global changes to a pattern of cells to be selected.

For example, if I wanted to select and delete 2 rows in a block, using say

Range ("A3:H3").Select and Range ("A66:H66").Select

then wanted to create another procedure which would select:

Range ("A3:H4").Select and Range ("A66:H67").Select etc etc

How would I do this?