Need a macro to evaluate and sift-through data
 

I need to write a macro to do the following:

1) Remove any entire line when a value in column A
(labeled as JobCode) begins with a letter "R":

For instance -

Column A Column B
A539292 This line is a test
B192591 This line is another test
R182571 This entire line should be removed
C194712 This line is yet another test

2) Sort the data by Column C (already have this part done)

3) Sift through the data in Column C and inserting 2
blank lines after each time the first 4 digits in column C
changes:

For instance -

Column B Column C
Testing Line 2601999999
Testing Line 2601999999
Testing Line 2601999999
(blank line)
(blank line)
Testing Line 2602999999
Testing Line 2602999999
(blank line)
(blank line)
Testing Line 2603999999 (etc)

4) And if possible, while inserting the two blank lines,
is there something else I can do to automatically add what
is in Column D together between these blank lines and
change the font of this total? When I do this manually, I
use the AutoSum button and it works only because of the
blank lines seperating the rows.

Example:

Column C Column D
2601999999 1
2601999999 1
(blank) (total: 2 in red)
(blank line)
2602999999 1
2602999999 2
(blank) (total: 3 in red) etc.

As usual, thank you to everyone in advance who can help!
If it helps, my current macro labels the column headers
(which might be unnecessary). Also, my attempts to do
most of this have failed because whenever I wanted it to
evaluate 1 column, it continued to the next column. I've
since then removed that coding due to fustration... :-/

Confused as always,
Ronny

Need a macro to evaluate and sift-through data
 

Hi
some ideas:
1. remove rows:
Sub delete_rows()
Dim lastrow As Long
Dim row_index As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For row_index = lastrow - 1 To 1 Step -1
If Left(Cells(row_index, "A").Value,1) = "R" then
Cells(row_index, "A").EntireRow.delete
End If
Next
Application.ScreenUpdating = True
End Sub

2. Sorting: you said you already did this :-)

3. Inserting rows / 4. Adding formulas:
Sub insert_rows()
Dim lastrow As Long
Dim row_index As Long

lastrow = ActiveSheet.Cells(Rows.count, "C").End(xlUp).row
For row_index = lastrow - 1 To 1 Step -1
If Left(Cells(row_index, "C").Value,4) < Left(Cells(row_index + 1,
"C").Value,4) _
Then
Cells(row_index + 1, "C").resize(2,1).EntireRow.Insert
(xlShiftDown)
Cells(row_index + 1, "D").formulaR1C1= _

"=SUMPRODUCT(--(LEFT(R1C3:R[-1]C3,4)=LEFT(R[-1]C3,4)),R1C4:R[-1]C4)"
Cells(row_index + 1, "D").font.colorindex=3
End If
Next
End Sub



--
Regards
Frank Kabel
Frankfurt, Germany

"Ronny Hamida" schrieb im Newsbeitrag
...
I need to write a macro to do the following:

1) Remove any entire line when a value in column A
(labeled as JobCode) begins with a letter "R":

For instance -

Column A Column B
A539292 This line is a test
B192591 This line is another test
R182571 This entire line should be removed
C194712 This line is yet another test

2) Sort the data by Column C (already have this part done)

3) Sift through the data in Column C and inserting 2
blank lines after each time the first 4 digits in column C
changes:

For instance -

Column B Column C
Testing Line 2601999999
Testing Line 2601999999
Testing Line 2601999999
(blank line)
(blank line)
Testing Line 2602999999
Testing Line 2602999999
(blank line)
(blank line)
Testing Line 2603999999 (etc)

4) And if possible, while inserting the two blank lines,
is there something else I can do to automatically add what
is in Column D together between these blank lines and
change the font of this total? When I do this manually, I
use the AutoSum button and it works only because of the
blank lines seperating the rows.

Example:

Column C Column D
2601999999 1
2601999999 1
(blank) (total: 2 in red)
(blank line)
2602999999 1
2602999999 2
(blank) (total: 3 in red) etc.

As usual, thank you to everyone in advance who can help!
If it helps, my current macro labels the column headers
(which might be unnecessary). Also, my attempts to do
most of this have failed because whenever I wanted it to
evaluate 1 column, it continued to the next column. I've
since then removed that coding due to fustration... :-/

Confused as always,
Ronny

Need a macro to evaluate and sift-through data
 

Thank you, Frank, for the help, however there's just one
more dilema with the code for inserting blank lines and
totalling up Column D:

I think the code you gave goes from the bottom of the
spreadsheet, up. When it does this, the blank lines occur
just fine, but the totalling up (of each section) does
not. While watching the macro, it seems that the
totalling up works fine until more blank lines are put in
(seperating due to the data in Column C).

Example of what happens during the macro run (not a
completed run):
Column C Column D
2601999999 1
2601999999 1
2602999999 1
2602999999 2
(blank) (total: 5)
(blank line)
2604999999 5
2604999999 8
(blank) (total: #VALUE!)
(blank line)

^^^^^^^^^
A numerical value occurs at first until the blank lines
are inserted above. That's when I get the #VALUE! Also,
the numerical value that appears before the blank lines
are made above is incorrect (as seen where it
says, "total: 5") because the data before that has not
been processed for the Column C change yet. Does that
make any sense?

Is there any way to change it so that it goes down the
list? And if so, how can I accomodate a header (since the
header would be different than the data, thus, two blank
lines would occur under it)?

Thank you again!
Ronny

-----Original Message-----
Hi
some ideas:
1. remove rows:
Sub delete_rows()
Dim lastrow As Long
Dim row_index As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For row_index = lastrow - 1 To 1 Step -1
If Left(Cells(row_index, "A").Value,1) = "R" then
Cells(row_index, "A").EntireRow.delete
End If
Next
Application.ScreenUpdating = True
End Sub

2. Sorting: you said you already did this :-)

3. Inserting rows / 4. Adding formulas:
Sub insert_rows()
Dim lastrow As Long
Dim row_index As Long

lastrow = ActiveSheet.Cells(Rows.count, "C").End(xlUp).row
For row_index = lastrow - 1 To 1 Step -1
If Left(Cells(row_index, "C").Value,4) < Left(Cells
(row_index + 1,
"C").Value,4) _
Then
Cells(row_index + 1, "C").resize
(2,1).EntireRow.Insert
(xlShiftDown)
Cells(row_index + 1, "D").formulaR1C1= _

"=SUMPRODUCT(--(LEFT(R1C3:R[-1]C3,4)=LEFT(R[-1]
C3,4)),R1C4:R[-1]C4)"
Cells(row_index + 1, "D").font.colorindex=3
End If
Next
End Sub



--
Regards
Frank Kabel
Frankfurt, Germany

"Ronny Hamida" schrieb im
Newsbeitrag
...
I need to write a macro to do the following:

1) Remove any entire line when a value in column A
(labeled as JobCode) begins with a letter "R":

For instance -

Column A Column B
A539292 This line is a test
B192591 This line is another test
R182571 This entire line should be removed
C194712 This line is yet another test

2) Sort the data by Column C (already have this part
done)

3) Sift through the data in Column C and inserting 2
blank lines after each time the first 4 digits in
column C
changes:

For instance -

Column B Column C
Testing Line 2601999999
Testing Line 2601999999
Testing Line 2601999999
(blank line)
(blank line)
Testing Line 2602999999
Testing Line 2602999999
(blank line)
(blank line)
Testing Line 2603999999 (etc)

4) And if possible, while inserting the two blank lines,
is there something else I can do to automatically add
what
is in Column D together between these blank lines and
change the font of this total? When I do this
manually, I
use the AutoSum button and it works only because of the
blank lines seperating the rows.

Example:

Column C Column D
2601999999 1
2601999999 1
(blank) (total: 2 in red)
(blank line)
2602999999 1
2602999999 2
(blank) (total: 3 in red) etc.

As usual, thank you to everyone in advance who can help!
If it helps, my current macro labels the column headers
(which might be unnecessary). Also, my attempts to do
most of this have failed because whenever I wanted it to
evaluate 1 column, it continued to the next column.
I've
since then removed that coding due to fustration... :-/

Confused as always,
Ronny


.