Solved

Macro spacing

Posted on 2014-01-20
22
322 Views
Last Modified: 2014-01-27
I have a sting of data in a column as shown below. Want to creat a macro to separare each identical data listing with a row space. The macro would stop upon detecting a "no data" column.

The number of identical data columns (3 in the example) ,length(3-4-7 in the example), and location of the columns will be variable. The macro would be run after placing the cursor on the first 5.

Since I have no clue in VBA coding, the results must be achieved using the MACRO RECORDER feature in Excel

                original                              after running macro

                  5                                                   5
                  5                                                   5
                  5                                                   5
                  6                          
                  6                                                   6
                  6                                                   6
                  6                                                   6
                  7                                                   6
                  7                                                  
                  7                                                   7
                  7                                                   7
                  7                                                   7
                  7                                                   7
                  7                                                   7
                                                                       7    
                                                                       7
0
Comment
Question by:radrick60
  • 10
  • 7
  • 3
  • +1
22 Comments
 
LVL 26

Expert Comment

by:MacroShadow
Comment Utility
Copy this code to a standard module. The macro will insert an empty row after each group in the column where the selection (cursor) is.

Sub Demo()
    Dim rng As Range, c As Range
    Set rng = Range(Selection.Column & ":" & Cells(Rows.Count, Selection.Column).End(xlUp).Row)
    For Each c In rng
        On Error Resume Next
        If c <> c.Offset(-1).Value And c.Offset(-1).Value <> Empty Then
            If Not Err.Number <> 0 Then
                c.EntireRow.Insert shift:=xlDown
            End If
        End If
    Next
End Sub

Open in new window

0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
The macro recorder does not have any capability of doing "Conditional" recording. It can only show the commands required to perform an operation. Any application of condition or looping has to be done manually. Do you want some code to do this?
0
 

Author Comment

by:radrick60
Comment Utility
Please bear with me. I am not at all familiar with VBA code, and have never inserted VBA code to run a macro. The only macros I have used were generated via the macro recorder, and of course the VBA code was generated (somewhere?) and was of no consequence to me.

Questions related to trying the macro:

1. I have opened the Development tab and copied the VBA code into a sheet.. Is this the correct procedure to utilize the macro  on all my files?

2. How do I name and execute the macro?

3. If i wanted to create 3 spaces instead on 1, as requested, is there a "tweak" to the code to accomplish this or is new code required.
0
 
LVL 26

Expert Comment

by:MacroShadow
Comment Utility
Attached you will find a working example with two macros; OriginalDemo and ModifiedDemo.
OriginalDemo will separate groups with one row and ModifiedDemo will separate with three rows.
Here you will find a tutorial on using macros.
Here's another short tutorial.
EE-Demo.xlsm
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
Here is how I show people to implement code

Right-click on the sheet tab name
Select View code
From the menu      Insert > Module
Paste the given code in the VBA window
Close the VBA window
Press Alt-F8
Select the macro name
Click on run
0
 

Author Comment

by:radrick60
Comment Utility
Syed:

I downloaded the file EE-Demo.xlsm, and tried to run the macro.

Got an error message: Object variable with block variable no set ????

NEXT TRY:

Pasted the code from MacroShadow per your instructions above.

Tried to run the macro and got  a message: Because of security settings, macro has been disabled

Got to security and checked the box allowing all macros ..... tried to run it again and got the same error message.

Created a different macro via the Macro Recorder and it worked ..... security allowed it to run??

For security reasons, would like to allow macros to run that I have "signed' off on.

Can you give me some guidance on these issues.

Seems like Microsoft didnt do a very good job in clarification of macro use
0
 
LVL 11

Expert Comment

by:tel2
Comment Utility
FYI, I downloaded EE-demo.xlsm and ran opened it with Excel 2003 (with the compatibility pack installed) and it opened without an error.

My security settings caused me to be asked if I wanted to enable macros, and I did.

I could run the OriginalMacro and the ModifiedMacro without error.

So, not sure what this prooves.  Some issue of compatibility with Excel 2013, perhaps?  That's the version you've got, right radrick?
0
 
LVL 26

Expert Comment

by:MacroShadow
Comment Utility
Check out my article http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/A_10805-Overcome-the-Trust-Center-Nuisance.html and see if it helps you.

As tel2 wrote, both macros work (in Excel 2010). I tested them before i posted. If the issue is related to security settings my article should help.
0
 
LVL 11

Expert Comment

by:tel2
Comment Utility
> As tel2 wrote, both macros work (in Excel 2010).
I said Excel 2003.
0
 
LVL 26

Expert Comment

by:MacroShadow
Comment Utility
"in Excel 2010" is my own addition that's why it's in parentheses.
0
 
LVL 11

Expert Comment

by:tel2
Comment Utility
So what is the relevance of Excel 2010, MacroShadow?  Is that the version you use?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 26

Expert Comment

by:MacroShadow
Comment Utility
So what is the relevance of Excel 2010, MacroShadow?  Is that the version you use?
Yes it is. But let's not get off-topic, this isn't are question to mess with!
0
 

Author Comment

by:radrick60
Comment Utility
Somehow I copied the code created (ABOVE) by MacroShadow

Put cursor on the "top" 5 and Ran the macro

        5    10   A   N   51
        5    11   B   O   52
        5    12   C   P    53
        6    13   D   Q   54
        6    14   E    R   55
        6    15   F    S    56
        6    16  G    T    57
        7    17  H    U   58
        7    18   I    V    59
        7    19   J    W   60
        7    20  K    X    61
        7    21  L    Y    62
        7    22  M  Z     63

The results of running the macro yielded a spacing of 4 betweem 5,6 and 7.

Spacing seems to change with the number of columns added

Desired results are as below ( i.e. row spacing is always 3)

 
        5    10   A   N   51
        5    11   B   O   52
        5    12   C   P    53


     
        6    13   D   Q   54
        6    14   E    R   55
        6    15   F    S    56
        6    16  G    T    57


     
        7    17  H    U   58
        7    18   I    V    59
        7    19   J    W   60
        7    20  K    X    61
        7    21  L    Y    62
        7    22  M  Z     63

Number of columns will vary and the number of identical rows will vary in my application

i AM USING 2013 EXCEL
0
 
LVL 26

Expert Comment

by:MacroShadow
Comment Utility
Nice catch.

Option Explicit

Sub ModifiedDemo()
    Dim rng As Range, c As Range
    Dim i As Integer
    Dim strColLetter As String
    strColLetter = ColumnLetter(Selection.Column)
    Set rng = Range(strColLetter & Selection.Row & ":" & strColLetter & Cells(Rows.Count, Selection.Column).End(xlUp).Row)
    For Each c In rng
        On Error Resume Next
        If c <> c.Offset(-1).Value And c.Offset(-1).Value <> Empty Then
            If Not Err.Number <> 0 Then
                For i = 1 To 3
                    c.EntireRow.Insert shift:=xlDown
                Next i
            End If
        End If
    Next
End Sub

Sub OriginalDemo()
    Dim rng As Range, c As Range
    Set rng = Range(strColLetter & Selection.Row & ":" & strColLetter & Cells(Rows.Count, Selection.Column).End(xlUp).Row)
    For Each c In rng
        On Error Resume Next
        If c <> c.Offset(-1).Value And c.Offset(-1).Value <> Empty Then
            If Not Err.Number <> 0 Then
                c.EntireRow.Insert shift:=xlDown
            End If
        End If
    Next
End Sub

Function ColumnLetter(col As Integer) As String

    Dim arr As String
    Dim remainder As Integer, devisor As Integer

    arr = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    If col > 26 Then
        remainder = col Mod 26
        devisor = (col - remainder) / 26
        If remainder > 0 Then
            ColumnLetter = Mid(arr, devisor, 1) & Mid(arr, remainder, 1)
        Else
            ColumnLetter = Mid(arr, devisor - 1, 1) & Mid(arr, 26, 1)
        End If
    Else
        ColumnLetter = Mid(arr, col, 1)
    End If

End Function

Open in new window

0
 

Author Comment

by:radrick60
Comment Utility
On sheet1, the macro works great. Creates the 3 spaces between the identical numbers

On sheet2, which is very representative of type sheets i will be working with, the macro does not work.

Tried placing the cursor at B3 and J3 and then initiated the macro. Nothing happens?
TESTINGMACRO.xlsm
0
 
LVL 26

Expert Comment

by:MacroShadow
Comment Utility
Put the code in a new module.
TESTINGMACRO.xlsm
0
 

Author Comment

by:radrick60
Comment Utility
Forgive me for my low tech skills ...

What do you mean "put the code in a new module".

Please describe how one does that.

Did you run the macro on sheet2?
0
 
LVL 26

Expert Comment

by:MacroShadow
Comment Utility
The code works fine on sheet2 too. The problem you had is due to the fact that the code resides in the sheet1 module which causes it to not be accessible by any other sheet.

Look at the attachment I posted you'll see the code is in a regular module. To insert a new module follow these steps:
1. Open the VBE (Visual Basic Editor) You can use the Alt+F11 key combination to open it.
2. On the menu click on insert
3. Click Module
4. Copy the code
0
 

Author Comment

by:radrick60
Comment Utility
The macro works GREAT on sheet2 !!

SOME HOUSECLEANING:

THERE ARE 5 MACROS LISTED WHEN I PRESS VIEW MACROS

Modified demo ..... this works
Original demo
3 others

Can i delete all but the Modified demo ?

Currently the macro is in THis Worksheet
If I select all open worksheets, can I expect it work in any worksheet that is opened or do i have to open another module?

And finally, how can i assign a CRTL+ALT key stroke to this macro
0
 
LVL 26

Expert Comment

by:MacroShadow
Comment Utility
Modified demo is what you want, this is all the code you need for it:
Option Explicit

Sub ModifiedDemo()
    Dim rng As Range, c As Range
    Dim i As Integer
    Dim strColLetter As String
    strColLetter = ColumnLetter(Selection.Column)
    Set rng = Range(strColLetter & Selection.Row & ":" & strColLetter & Cells(Rows.Count, Selection.Column).End(xlUp).Row)
    For Each c In rng
        On Error Resume Next
        If c <> c.Offset(-1).Value And c.Offset(-1).Value <> Empty Then
            If Not Err.Number <> 0 Then
                For i = 1 To 3
                    c.EntireRow.Insert shift:=xlDown
                Next i
            End If
        End If
    Next
End Sub

Function ColumnLetter(col As Integer) As String

    Dim arr As String
    Dim remainder As Integer, devisor As Integer

    arr = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    If col > 26 Then
        remainder = col Mod 26
        devisor = (col - remainder) / 26
        If remainder > 0 Then
            ColumnLetter = Mid(arr, devisor, 1) & Mid(arr, remainder, 1)
        Else
            ColumnLetter = Mid(arr, devisor - 1, 1) & Mid(arr, 26, 1)
        End If
    Else
        ColumnLetter = Mid(arr, col, 1)
    End If

End Function

Open in new window


Currently the macro is in THis Worksheet
If I select all open worksheets, can I expect it work in any worksheet that is opened or do i have to open another module?
As I wrote put the code in a new module, thus the macro will work for any sheet it that workbook.

And finally, how can i assign a CRTL+ALT key stroke to this macro

1.

Click File, and then click Options

2.

Click Customize Ribbon

3.

Next to Keyboard shortcuts, click Customize.

4.

In the Categories list, click Macros.

5.

In the Macros list, click the macro that you want to change.

6.

In the Press new shortcut key box, type the key combination that you want to choose.

7.

Check the Current keys box to make sure that you aren't assigning a key combination that you already use to perform a different task.

8.

In the Save changes in list, click the option that matches where you want to run your macro.

9.

Click Close.
0
 

Author Comment

by:radrick60
Comment Utility
MacroShadow:

Have tried the macro on several worksheets and it works fine.

For awhile I didnt think it was going to happen

Now that its working, I have thought of more uses in other applications.

I am embarest to ask you this, but the other applications requires 4 spaces instead of 3 spaces. Can I easily edit the code to get the extra space? The 4 spaces code will accomodate both my applications .... no need for two different code sets.

Thanx for your help and  patience
0
 
LVL 26

Accepted Solution

by:
MacroShadow earned 500 total points
Comment Utility
Just change this line:
For i = 1 To 3

Open in new window

to this:
For i = 1 To 4

Open in new window

0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now