Standardising the gap between blocks of text

[chadski778]

I have two blocks of data in column A and they vary in size in each day (between A1 and A100) separated by empty rows. Could I please have code that will make the gap between the blocks always be 3 empty rows? I would like this automated so that I can fit it into another macro

 

[hiker95]

chadski778,

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

 

[chadski778]

Sorry I should have said the first block of rows starts as A1 and can vary from A1:A5 up to A1:A32. The second block of rows of data starts at A37 and can extend anywhere up to A37:A60

 

[chadski778]

OK I will post them this evening because I cannot install the add-on using my work computer. I am using Excel 2007

 

[chadski778]

Excel Workbook
	A	B	C	D	E	F
1	BEFORE
2
3	Generic Name
4	Highly refined mineral oil (IP346
5	Highly refined mineral oil (IP346
6	Highly refined mineral oil (IP346
7	Highly refined mineral oil (IP346
8	Highly refined mineral oil (IP346
9	Polyolefin polyamine succinimide polyol
10	Calcium alkaryl sulphonate
11	Zinc dialkyldithiophosphate
12	Olefin co-polymer
13	Highly refined mineral oil (IP346
14	Polyalkylmethacrylate
15	Calcium alkaryl sulphonate
16	Borated polyolefin polyamine succinimide
17	Polyolefin polyamine succinimide molybdenum cmplx
18	Polyalkarylamine
19	Branched alkanes
20	Alkyl methacrylate (as impurity)
21
22
23
24
25
26
27
28
29
30
31
32
33
34	Risk phrase key
35	N/C = Not classified as hazardous under EC Directives
36	R41 = Risk of serious damage to eyes
37	R53 = May cause long-term adverse effects in the aquatic environment
38	R51/53 = Toxic to aquatic organisms, may cause long-term adverse effects in the aquatic environment
39	R43 = May cause sensitisation by skin contact
40	R38 = Irritating to skin
41	R65 = Harmful: may cause lung damage if swallowed
42	R36/37/38 = Irritating to eyes, respiratory system and skin
43	R50/53 = Very toxic to aquatic organisms, may cause long-term adverse effects in the aquatic environment
44
45
46
47
48
CasDoc

Excel Workbook
	A	B	C	D	E	F
1	AFTER
2
3	Generic Name
4	Highly refined mineral oil (IP346
5	Highly refined mineral oil (IP346
6	Highly refined mineral oil (IP346
7	Highly refined mineral oil (IP346
8	Highly refined mineral oil (IP346
9	Polyolefin polyamine succinimide polyol
10	Calcium alkaryl sulphonate
11	Zinc dialkyldithiophosphate
12	Olefin co-polymer
13	Highly refined mineral oil (IP346
14	Polyalkylmethacrylate
15	Calcium alkaryl sulphonate
16	Borated polyolefin polyamine succinimide
17	Polyolefin polyamine succinimide molybdenum cmplx
18	Polyalkarylamine
19	Branched alkanes
20	Alkyl methacrylate (as impurity)
21
22
23	Risk phrase key
24	N/C = Not classified as hazardous under EC Directives
25	R41 = Risk of serious damage to eyes
26	R53 = May cause long-term adverse effects in the aquatic environment
27	R51/53 = Toxic to aquatic organisms, may cause long-term adverse effects in the aquatic environment
28	R43 = May cause sensitisation by skin contact
29	R38 = Irritating to skin
30	R65 = Harmful: may cause lung damage if swallowed
31	R36/37/38 = Irritating to eyes, respiratory system and skin
32	R50/53 = Very toxic to aquatic organisms, may cause long-term adverse effects in the aquatic environment
33
CasDoc

Note - 'Generic name' is in A1 not A3 like in the diagram and the first block of data can vary in size from 4 rows to anything up to 30

 

[hiker95]

chadski778,


For the macro to work correctly, there must be at least one blank row between the two groups in column A in worksheet CasDoc.


Sample raw data in worksheet CasDoc:

Excel Workbook
	A
1	Generic Name
2	Highly refined mineral oil (IP346
3	Highly refined mineral oil (IP346
4	Highly refined mineral oil (IP346
5	Highly refined mineral oil (IP346
6	Highly refined mineral oil (IP346
7	Polyolefin polyamine succinimide polyol
8	Calcium alkaryl sulphonate
9	Zinc dialkyldithiophosphate
10	Olefin co-polymer
11	Highly refined mineral oil (IP346
12	Polyalkylmethacrylate
13	Calcium alkaryl sulphonate
14	Borated polyolefin polyamine succinimide
15	Polyolefin polyamine succinimide molybdenum cmplx
16	Polyalkarylamine
17	Branched alkanes
18	Alkyl methacrylate (as impurity)
19
20
21
22
23
24
25
26
27
28
29
30
31
32	Risk phrase key
33	N/C = Not classified as hazardous under EC Directives
34	R41 = Risk of serious damage to eyes
35	R53 = May cause long-term adverse effects in the aquatic environment
36	R51/53 = Toxic to aquatic organisms, may cause long-term adverse effects in the aquatic environment
37	R43 = May cause sensitisation by skin contact
38	R38 = Irritating to skin
39	R65 = Harmful: may cause lung damage if swallowed
40	R36/37/38 = Irritating to eyes, respiratory system and skin
41	R50/53 = Very toxic to aquatic organisms, may cause long-term adverse effects in the aquatic environment
42
CasDoc

After the macro:

Excel Workbook
	A
1	Generic Name
2	Highly refined mineral oil (IP346
3	Highly refined mineral oil (IP346
4	Highly refined mineral oil (IP346
5	Highly refined mineral oil (IP346
6	Highly refined mineral oil (IP346
7	Polyolefin polyamine succinimide polyol
8	Calcium alkaryl sulphonate
9	Zinc dialkyldithiophosphate
10	Olefin co-polymer
11	Highly refined mineral oil (IP346
12	Polyalkylmethacrylate
13	Calcium alkaryl sulphonate
14	Borated polyolefin polyamine succinimide
15	Polyolefin polyamine succinimide molybdenum cmplx
16	Polyalkarylamine
17	Branched alkanes
18	Alkyl methacrylate (as impurity)
19
20
21
22	Risk phrase key
23	N/C = Not classified as hazardous under EC Directives
24	R41 = Risk of serious damage to eyes
25	R53 = May cause long-term adverse effects in the aquatic environment
26	R51/53 = Toxic to aquatic organisms, may cause long-term adverse effects in the aquatic environment
27	R43 = May cause sensitisation by skin contact
28	R38 = Irritating to skin
29	R65 = Harmful: may cause lung damage if swallowed
30	R36/37/38 = Irritating to eyes, respiratory system and skin
31	R50/53 = Very toxic to aquatic organisms, may cause long-term adverse effects in the aquatic environment
32
CasDoc

Sample raw data in worksheet CasDoc (with at least one blank row between the groups) before the macro:

Excel Workbook
	A
1	Generic Name
2	Highly refined mineral oil (IP346
3	Highly refined mineral oil (IP346
4	Highly refined mineral oil (IP346
5	Highly refined mineral oil (IP346
6	Highly refined mineral oil (IP346
7	Polyolefin polyamine succinimide polyol
8	Calcium alkaryl sulphonate
9	Zinc dialkyldithiophosphate
10	Olefin co-polymer
11	Highly refined mineral oil (IP346
12	Polyalkylmethacrylate
13	Calcium alkaryl sulphonate
14	Borated polyolefin polyamine succinimide
15	Polyolefin polyamine succinimide molybdenum cmplx
16	Polyalkarylamine
17	Branched alkanes
18	Alkyl methacrylate (as impurity)
19
20	Risk phrase key
21	N/C = Not classified as hazardous under EC Directives
22	R41 = Risk of serious damage to eyes
23	R53 = May cause long-term adverse effects in the aquatic environment
24	R51/53 = Toxic to aquatic organisms, may cause long-term adverse effects in the aquatic environment
25	R43 = May cause sensitisation by skin contact
26	R38 = Irritating to skin
27	R65 = Harmful: may cause lung damage if swallowed
28	R36/37/38 = Irritating to eyes, respiratory system and skin
29	R50/53 = Very toxic to aquatic organisms, may cause long-term adverse effects in the aquatic environment
30
CasDoc

After the macro:

Excel Workbook
	A
1	Generic Name
2	Highly refined mineral oil (IP346
3	Highly refined mineral oil (IP346
4	Highly refined mineral oil (IP346
5	Highly refined mineral oil (IP346
6	Highly refined mineral oil (IP346
7	Polyolefin polyamine succinimide polyol
8	Calcium alkaryl sulphonate
9	Zinc dialkyldithiophosphate
10	Olefin co-polymer
11	Highly refined mineral oil (IP346
12	Polyalkylmethacrylate
13	Calcium alkaryl sulphonate
14	Borated polyolefin polyamine succinimide
15	Polyolefin polyamine succinimide molybdenum cmplx
16	Polyalkarylamine
17	Branched alkanes
18	Alkyl methacrylate (as impurity)
19
20
21
22	Risk phrase key
23	N/C = Not classified as hazardous under EC Directives
24	R41 = Risk of serious damage to eyes
25	R53 = May cause long-term adverse effects in the aquatic environment
26	R51/53 = Toxic to aquatic organisms, may cause long-term adverse effects in the aquatic environment
27	R43 = May cause sensitisation by skin contact
28	R38 = Irritating to skin
29	R65 = Harmful: may cause lung damage if swallowed
30	R36/37/38 = Irritating to eyes, respiratory system and skin
31	R50/53 = Very toxic to aquatic organisms, may cause long-term adverse effects in the aquatic environment
32
CasDoc

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ThreeRowGap()
' hiker95, 08/11/2011
' http://www.mrexcel.com/forum/showthread.php?t=570998
Dim ER As Long, NSR As Long
Worksheets("CasDoc").Activate
ER = Range("A1").End(xlDown).Row
NSR = Range("A" & ER).End(xlDown).Row
Select Case NSR - ER
  Case 2
    Rows(NSR).Resize(2).Insert
  Case 3
    Rows(NSR).Resize(1).Insert
  Case 4
    'do nothing
  Case Is > 4
    Rows(ER + 4 & ":" & NSR - 1).Delete
End Select
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the ThreeRowGap macro.

 

[Akinrotimi]

The code below would promptly solve the problem:

Option Explicit
Sub ThreeRowGap()
' akinrotimi, 08/11/2011
' http://www.mrexcel.com/forum/showthread.php?p=2824989#post2824989
 
    Application.ScreenUpdating = False
    Range("a3").End(xlDown).Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "qwerty123"
    Range("a3").End(xlDown).Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "qwerty123"
    Range("a3").End(xlDown).Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "qwerty123"
    Columns("a:a").Select
    Selection.TextToColumns Destination:=Range("a1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 1), TrailingMinusNumbers:=True
    Columns("a:a").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Columns("a:a").Select
    Selection.Replace What:="qwerty123", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("A1").Select
    Application.ScreenUpdating = False
End Sub

Let me know if you still have any issues.

Regards

Rotimi

 

[hiker95]

akinrotimi,

Interesting approach. One for the archives.

I have removed all the Select statements (the code will execute faster).

Sub ThreeRowGap_akinrotimi()
' akinrotimi, 08/11/2011
' http://www.mrexcel.com/forum/showthr...89#post2824989
Application.ScreenUpdating = False
Range("a3").End(xlDown).Offset(1, 0).Resize(3).FormulaR1C1 = "qwerty123"
With Columns(1)
  .TextToColumns Destination:=Range("a1"), DataType:=xlFixedWidth, _
    FieldInfo:=Array(0, 1), TrailingMinusNumbers:=True
  .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  .Replace What:="qwerty123", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
End With
Application.ScreenUpdating = False
End Sub

 

[chadski778]

Thanks guys it's working!

 

[Akinrotimi]

Chadski,

I am happy the solution proffered works as stated.That was and remains Bill's vision and that is the reason I am here.

Some of us need to learn not to be superfluously aggressive,forward or froward as decorum is a minimal requirement in a forum like this.This is a simple case of six(6) and half-dozen.As they say,less is more!

Once again,thanks for appreciating the solution.

Regards

Rotimi