English to VBA translator needed
 

This is what im trying to get a macro to do if anyone can help put thi
into a VBA code it would be a big help to me.


Search Range A1:H1000 for any instances where there is a value i
column A and
adjacent cells in row B:H are blank cells.
When found set range from the cell in column A where the above criteri
were satisfied to the last cell in column H that has consecutive value
in it

(By this I mean that if there are say values in cells H3:H6 then cell
H7:H8 are blank and H9 has a value in it the macro would only selec
the range down to H6)

For this range that has been selected only copy rows within this rang
if they have a "Y" Value in column "I"

Now paste this into Sheet 2
Ensuring that there is a blank row above and below the group of dat
being pasted

eg,

BLANK ROW
XXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXX <-------DATA
XXXXXXXXXXXXXXXXXXX
BLANK ROW

Repeat this until the macro has gone down to row 1000

So in sheet two you would get your selected tables looking like this


BLANK ROW
XXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXX <-------DATA
XXXXXXXXXXXXXXXXXXX
BLANK ROW
BLANK ROW
XXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXX <-------DATA
XXXXXXXXXXXXXXXXXXX
BLANK ROW
BLANK ROW
XXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXX <-------DATA
XXXXXXXXXXXXXXXXXXX
BLANK ROW

In Sheet 2 Search Column A for any instances of the Word "Total" whe
found cut that row and paste it at the bottom of the table list so th
macro would look for the first 3 consecutive blank rows and when i
finds them it pastes any row with the word "total" in them one ro
after another.

So the when finished sheet 2 will look like this

BLANK ROW
XXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXX <-------DATA
XXXXXXXXXXXXXXXXXXX
BLANK ROW
BLANK ROW
XXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXX <-------DATA
XXXXXXXXXXXXXXXXXXX
BLANK ROW
BLANK ROW
XXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXX <-------DATA
XXXXXXXXXXXXXXXXXXX
BLANK ROW
BLANK ROW
BLANK ROW
TOTALXXXXXXXXXXXXXX
TOTALXXXXXXXXXXXXXX
TOTALXXXXXXXXXXXXXX<-------TOTAL DATA
TOTALXXXXXXXXXXXXXX
TOTALXXXXXXXXXXXXX

--
Vlad99
-----------------------------------------------------------------------
Vlad999's Profile: http://www.excelforum.com/member.php...fo&userid=3358
View this thread: http://www.excelforum.com/showthread.php?threadid=53366

English to VBA translator needed
 

Assuming if H contains a blank, then the row should be copied

Sub ABC()
Dim rng As Range, ar As Range
Dim rng1 As Range, rng2 As Range
Dim cell As Range, c As Range
Set rng = Worksheets("Sheet1") _
.Range("H1:H1000").SpecialCells(xlBlanks)
For Each ar In rng.Areas
ar.EntireRow.Copy Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp)(4)
Next
With Worksheets("Sheet2")
If IsEmpty(.Cells(1, 1)) And IsEmpty(.Cells(2, 1)) Then
Set c = .Cells(1, 1).End(xlDown)
.Range(.Cells(1, 1), c.Offset(-2, 0)).EntireRow.Delete
End If
Set rng1 = .Range(.Range("A2"), .Cells(Rows.Count, 1).End(xlUp))

For Each cell In rng1
If InStr(1, cell, "total", vbTextCompare) Then
If rng2 Is Nothing Then
Set rng2 = cell
Else
Set rng2 = Union(rng2, cell)
End If
End If
Next
If Not rng2 Is Nothing Then
rng2.EntireRow.Copy rng1(rng1.Count).Offset(3, 0)
rng2.EntireRow.Delete
End If
End With

End Sub

--
Regards,
Tom Ogilvy

"Vlad999" wrote:


This is what im trying to get a macro to do if anyone can help put this
into a VBA code it would be a big help to me.


Search Range A1:H1000 for any instances where there is a value in
column A and
adjacent cells in row B:H are blank cells.
When found set range from the cell in column A where the above criteria
were satisfied to the last cell in column H that has consecutive values
in it

(By this I mean that if there are say values in cells H3:H6 then cells
H7:H8 are blank and H9 has a value in it the macro would only select
the range down to H6)

For this range that has been selected only copy rows within this range
if they have a "Y" Value in column "I"

Now paste this into Sheet 2
Ensuring that there is a blank row above and below the group of data
being pasted

eg,

BLANK ROW
XXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXX <-------DATA
XXXXXXXXXXXXXXXXXXX
BLANK ROW

Repeat this until the macro has gone down to row 1000

So in sheet two you would get your selected tables looking like this


BLANK ROW
XXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXX <-------DATA
XXXXXXXXXXXXXXXXXXX
BLANK ROW
BLANK ROW
XXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXX <-------DATA
XXXXXXXXXXXXXXXXXXX
BLANK ROW
BLANK ROW
XXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXX <-------DATA
XXXXXXXXXXXXXXXXXXX
BLANK ROW

In Sheet 2 Search Column A for any instances of the Word "Total" when
found cut that row and paste it at the bottom of the table list so the
macro would look for the first 3 consecutive blank rows and when it
finds them it pastes any row with the word "total" in them one row
after another.

So the when finished sheet 2 will look like this

BLANK ROW
XXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXX <-------DATA
XXXXXXXXXXXXXXXXXXX
BLANK ROW
BLANK ROW
XXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXX <-------DATA
XXXXXXXXXXXXXXXXXXX
BLANK ROW
BLANK ROW
XXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXX <-------DATA
XXXXXXXXXXXXXXXXXXX
BLANK ROW
BLANK ROW
BLANK ROW
TOTALXXXXXXXXXXXXXX
TOTALXXXXXXXXXXXXXX
TOTALXXXXXXXXXXXXXX<-------TOTAL DATA
TOTALXXXXXXXXXXXXXX
TOTALXXXXXXXXXXXXXX


--
Vlad999
------------------------------------------------------------------------
Vlad999's Profile: http://www.excelforum.com/member.php...o&userid=33586
View this thread: http://www.excelforum.com/showthread...hreadid=533661

English to VBA translator needed
 

Thank you VERY much Tom


--
Vlad999
------------------------------------------------------------------------
Vlad999's Profile: http://www.excelforum.com/member.php...o&userid=33586
View this thread: http://www.excelforum.com/showthread...hreadid=533661