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.
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
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!
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
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
Currently the macro is in THis WorksheetAs I wrote put the code in a new module, thus the macro will work for any sheet it that workbook.
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
If you are experiencing a similar issue, please ask a related question
Title | # Comments | Views | Activity |
---|---|---|---|
is VBA going to die in another decade? what is the Office.JS is it going to replace VBA? | 4 | 50 | |
And OR formula | 5 | 21 | |
Manipulate Range in Excel VBA | 6 | 21 | |
Excel Formula to check both condition's and return values | 2 | 26 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
19 Experts available now in Live!