Solved

Macro spacing

Posted on 2014-01-20
22
340 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 27

Expert Comment

by:MacroShadow
ID: 39796267
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
ID: 39796284
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
ID: 39796702
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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
LVL 27

Expert Comment

by:MacroShadow
ID: 39796758
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
ID: 39796937
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
ID: 39802113
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 12

Expert Comment

by:tel2
ID: 39802217
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 27

Expert Comment

by:MacroShadow
ID: 39802228
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 12

Expert Comment

by:tel2
ID: 39802231
> As tel2 wrote, both macros work (in Excel 2010).
I said Excel 2003.
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39802254
"in Excel 2010" is my own addition that's why it's in parentheses.
0
 
LVL 12

Expert Comment

by:tel2
ID: 39808001
So what is the relevance of Excel 2010, MacroShadow?  Is that the version you use?
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39808877
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
ID: 39809099
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 27

Expert Comment

by:MacroShadow
ID: 39809197
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
ID: 39810047
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 27

Expert Comment

by:MacroShadow
ID: 39810060
Put the code in a new module.
TESTINGMACRO.xlsm
0
 

Author Comment

by:radrick60
ID: 39810846
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 27

Expert Comment

by:MacroShadow
ID: 39811078
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
ID: 39811249
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 27

Expert Comment

by:MacroShadow
ID: 39811277
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
ID: 39811430
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 27

Accepted Solution

by:
MacroShadow earned 500 total points
ID: 39811450
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
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.

813 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

19 Experts available now in Live!

Get 1:1 Help Now