How do I Insert 2 or more rows with a copy from one other row?

[RonMcK]

Hi, All,

Add to the subject question, and do it without looping? What's the most efficient way to insert 1 or more rows at a specific point in a worksheet and then paste into each of them a range of data (the row above the insertion point)?

Looking at .Insert in Help, it appears I can do the copy and insert of one line at A3 with:

Range("A4").Insert(Shift, CopyOrigin) where Shift:=xlShiftDown

Can CopyOrigin (specified as a variant) be a range, such as Range("A" & myRow).Resize(myLastCol) ?

Can I extend this code in some way so it copies that line into 2 or more inserted lines without looping?

Thanks,

[mdmackillop]

Like

Option Explicit

Sub fills()
Dim x As Long
x = 3
ActiveCell(2).Resize(x).EntireRow.Insert
ActiveCell.EntireRow.Resize(x + 1).FillDown
End Sub

[RonMcK]

Thanks, Malcolm!

[RonMcK]

Malcolm, et al,

The next challenge is inserting 2 columns to the left of original column C. I tried the following, which I extrapolated from the code above.


Sub fill_down()
Dim x As Long
x = 3
Debug.Print ActiveCell.Row
ActiveCell(2).Resize(x).EntireColumn.Insert ' for columns
' ActiveCell.EntireColumn.Resize(x + 1).FillRight
' ActiveCell(2).Resize(x).EntireRow.Insert ' for rows
' ActiveCell.EntireRow.Resize(x + 1).FillDown
End Sub

No copying is needed into these new columns.

The challenge is that the above code only inserts 1 column to the left of the ActiveCell's column. So, A, B, NewC, NewD (was C), etc. Why is that?

And, what does the (2) in the after ActiveCell signify? when I look at the value of ActiveCell(2) it's the contents of the cell. How is this significant in your Row insert/filldown code and in my variation on your code?

So, then, I recorded a macro just to see how XL would code inserting two columns. Here's it's code which uses the accursed Select/Selection but is simple enough. What is the better way to accomplish this insertion?


Sub Macro1()
' Macro1 Macro
Columns("C:D").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub


Thanks,:help

[mdmackillop]

Resize takes two parameters Row & Column. If only one is used, this defaults to Rows. For Columns use


ActiveCell.Offset(,1).Resize(,x).EntireColumn.Insert

ActiveCell(2) is a lazy way of saying ActiveCell.Offset(1), so it would not be appropriate with columns

[RonMcK]

Two questions:

1. Is there a quick way to convert between column letters and column numbers? Or, do I just need to hunker down and work with chr() and asc()?

2. I want to add some column formatting to this worksheet, column widths, word wrap, align to top, etc. Here's what I captured with macro recorder.

Is selection my best tool or can this be done more elegantly another way? Perhaps with Range?


Sub Macro3()
' Macro3 Macro
Range("C1:C56").Select
Selection.ColumnWidth = 50
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub

Thanks,

[mdmackillop]

Hi Ron
You can get a column number as follows.

Dim c As Long
c = Range("C1").Column

I would always use something like this; never Selection.

With Cells(1, c).Resize(52)
.ColumnWidth = 50
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With


Consider using cell styles, which can hold multiple formatting values. The built in Comma style is one example


With Cells(1, c).Resize(52)
.ColumnWidth = 25
.Style = "comma"
End With

[RonMcK]

Hi Ron
You can get a column number as follows.

Dim c As Long
c = Range("C1").Column



That is sweet. It makes the following superfluous

Public Function DetColNum(ByVal ColLtr As String) As Integer
Dim temp As Integer, ColNum As Integer, x As Integer
ColNum = 0
If ColLtr <> UCase(ColLtr) Then
ColLtr = UCase(ColLtr)
End If
If Len(ColLtr) > 3 Or ColLtr > "XFD" Or Len(ColLtr) = 0 Then
MsgBox "Invalid Column Ltr Combination (Null or > XFD)" & vbCrLf & "Returning value of 1"
ColNum = 1
Exit Function
End If
x = Len(ColLtr)
Do While x >= 1
temp = Asc(Mid(ColLtr, x, 1)) - 64
Select Case x
Case 3
temp2 = temp
Case 2
temp2 = temp * 26
Case 1
temp2 = temp * 26 * 26
Case Else
MsgBox "Invalid character string passed to DetColNum." & vbCrLf & "Returning value of 1"
ColNum = 1
Exit Function
End Select
ColNum = ColNum + temp2
x = x - 1
Loop
DetColNum = ColNum
End Sub




I would always use something like this; never Selection.

With Cells(1, c).Resize(52)
.ColumnWidth = 50
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With


So, Cells.Resize uses the same attributes that Selection (from the macro) uses? Cool.

Question, Malcolm, what is the (52) after resize? Number of rows?




Consider using cell styles, which can hold multiple formatting values. The built in Comma style is one example


With Cells(1, c).Resize(52)
.ColumnWidth = 25
.Style = "comma"
End With


So, if I wanted to have borders, a different font, an interior color, etc., I would use multiple .style= statements picking up each of the attributes I want to set?

Thanks for your help in furthering my XL education.

Thanks,

[mdmackillop]

Hi Ron
52 is rows. You can use both Rows and Columns as in

Cells(1,1).Resize(52,10)

Styles can hold multiple formatting data in each style; see the attachment.

[RonMcK]

Hi, Malcolm,

I'm feeling dense at the moment. How do I assign a style name to a style? And do I have to define that style in a cell on the worksheet to be able to do this? Or, can I build it in my code and then apply it to my worksheet?

What's a good resource to read on Styles? The Help?

I see style1 and style2 assigned to the ranges in the code. I also see those names as labels on the worksheet. And, when I click on either of the sample cells and look at Format Cells, I see all the attributes (border, ital, bold, BP sign, colors). How do those specific combinations get 'named' style1 and style2. It doesn't appear to be Names Manager.

Thanks,

[mdmackillop]

In Excel 2000, Go to Format/Styles. You set all the values there and give it a name.
In 2010 it's Home/Styles/New Cell Style

[RonMcK]

In Excel 2000, Go to Format/Styles. You set all the values there and give it a name.
In 2010 it's Home/Styles/New Cell Style

Since I want any of my users to be able to use my VBA code, I will need to include something like the below macro's code in my VBA code, right? This will build the desired style is on each user's machine. Or is there a better way to do this?


Sub Macro5()
' Macro5 Macro
Range("C1").Select
ActiveWorkbook.Styles.Add Name:="Style myHdrRow"
With ActiveWorkbook.Styles("Style myHdrRow")
.IncludeNumber = True
.IncludeFont = True
.IncludeAlignment = True
.IncludeBorder = True
.IncludePatterns = True
.IncludeProtection = True
End With
With ActiveWorkbook.Styles("Style myHdrRow").Font
.Name = "Calibri"
.Size = 11
.Bold = True
.Italic = False
.Underline = xlUnderlineStyleNone
.Strikethrough = False
.ThemeColor = 2
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
With ActiveWorkbook.Styles("Style myHdrRow")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.ReadingOrder = xlContext
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
End With
With ActiveWorkbook.Styles("Style myHdrRow").Borders(xlLeft)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With ActiveWorkbook.Styles("Style myHdrRow").Borders(xlRight)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With ActiveWorkbook.Styles("Style myHdrRow").Borders(xlTop)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With ActiveWorkbook.Styles("Style myHdrRow").Borders(xlBottom)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
ActiveWorkbook.Styles("Style myHdrRow").Borders(xlDiagonalDown).LineStyle = _
xlNone
ActiveWorkbook.Styles("Style myHdrRow").Borders(xlDiagonalUp).LineStyle = _
xlNone
With ActiveWorkbook.Styles("Style myHdrRow").Interior
.Pattern = xlSolid
.PatternColorIndex = 0
.ThemeColor = xlThemeColorAccent4
.TintAndShade = 0.799981688894314
.PatternTintAndShade = 0
End With
End Sub


Thanks, Malcolm.

[mdmackillop]

If your users accessed the macros by using a workbook template or XLA add-in, the styles could be built into either of those.

[RonMcK]

Oh! (a flash of light!) I'll need to do some more research on how I want to package this for distribution to coworkers; for the moment, I'm the only on running this code and it's on my laptop, so, I can save the style and use it.

Thanks, Malcolm.

[mdmackillop]

Basically, if you only need to distribute the Macros for use in a variety of workbook applications, use an add-in. If there are pre-set worksheets etc. then use a template.

[RonMcK]

At the moment, what I have is a single "application" that will (or at least can be) reused regularly to create formatted and better organized files from tab-delimited text files produced by an application we use to build assessment products for our Science textbooks.

The pieces can be re-used and I plan to use them with other "programs" that I write, so, what's probably a template right now may well be come an add-in.

Thanks for your advice and guidance,

[RonMcK]

MD,

New problem in Ronald's little adventure in VBA programming.

I have a Sub (see below) and it's reporting an error on my .style assignment line. I defined myHdrRow as a style in my workbook; the VBA is opening another file and manipulating it, does that make a difference?


Sub Format_Col_Head_Row(LstCol As Long)
Dim c As Long
c = 1
With Cells(1, c).Resize(1, LstCol)
.Style = "myHdrRow"
End With
End Sub

Runtime error '450' Wrong number of arguments or invalid property assignment.

I crafted the above using your example as my model:


Consider using cell styles, which can hold multiple formatting values. The built in Comma style is one example




With Cells(1, c).Resize(52)
.ColumnWidth = 25
.Style = "comma"
End With


Thanks for helping me sort this out,

[RonMcK]

MD, earlier (#7, above) you showed me the simple way to get the column's number if I have the column letter.

Is there a quick way to get from a column number to its representation as a letter? Or is this a case where I need to use code?

If code is needed, here is something a co-worker gave me 4 or 5 years ago. I updated it this past weekend to cope with the larger width of 2007 & 2010:


Public Function DetColLtr(ByVal ColNum As Integer) As String
Dim DetColLtr As String
If ColNum < 27 Then
DetColLtr = Chr(ColNum + 64)
Else
If ColNum < 677 Then
DetColLtr = Chr(ColNum / 26 + 64) & Chr((ColNum Mod 26) + 64)
Else
If ColNum < 17577 Then
DetColLtr = Chr(ColNum / 676 + 64) & Chr((ColNum Mod 676) / 26 + 64) & Chr((ColNum Mod 26) + 64)
End If
End If
End If
End Function

[mdmackillop]

I've learned more about styles now! (I rarely use them myself).

You need to transfer the styles to the second book. This can be done using Merge Styles.

It is good practice to pass the destination sheet to the second sub to ensure the desired sheet is changed.


Sub test()
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim ws As Worksheet
Set wb1 = ActiveWorkbook
Set wb2 = Workbooks("Book2.xls")
Set ws = wb2.Sheets(1)
On Error Resume Next
wb2.Styles.Merge Workbook:=wb1
On Error GoTo 0
Format_Col_Head_Row ws, 3
End Sub

Sub Format_Col_Head_Row(ws As Worksheet, LstCol As Long)
Dim c As Long
c = 1
With ws.Cells(1, c).Resize(1, LstCol)
.Style = "MyHdrRow"
End With
End Sub

[mdmackillop]

MD, earlier (#7, above) you showed me the simple way to get the column's number if I have the column letter.



The words "sledghammer" and "nut" spring to mind


Sub Test2()
MsgBox "Column " & MyCol(28)
End Sub

Function MyCol(c)
MyCol = Split(Cells(1, c).Address, "$")(1)
End Function

[RonMcK]

Malcolm,

Thank you very much!! :ole:
:mbounce: :hifive: :clap2:



Have a pleasant rest.

[RonMcK]

Malcolm,

I needed to make a few changes, wb2 is the activeworkbook not wb1. I ended up using the following and then the formatting happened as I envisioned.


Sub CopyStyles()
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim ws As Worksheet
Set wb1 = Workbooks("Ron's Row Inserting and Updating Program.xlsm")
Set wb2 = Workbooks("g3_INFO.xls")
Set ws = wb2.Sheets("INFO")
On Error Resume Next
wb2.Styles.Merge Workbook:=wb1
On Error GoTo 0
Format_Col_Head_Row2 ws, LastCol
End Sub

Sub Format_Col_Head_Row2(ws1 As Worksheet, LstCol As Long)
Dim c As Long
LstCol = Range("BO" & 1).Column
Debug.Print LstCol
c = 1
With ws1.Cells(1, c).Resize(1, LstCol)
.Style = "MyHdrRow"
End With
End Sub

We're pressing right along. I may get this completed tomorrow.

Thanks, again,

[RonMcK]

I am adding code to the program to name and save the text file after it's pulled into XL. Then, when processing is complete, I want to save it over itself.

The apparent challenge is that wbs.SaveAs does not appear to have an Overwrite:=True option so my user will have to deal with a pop-up noting the file exists and asking if one wants to overwrite.

Are my choices then either one of skipping the initial save and doing the naming at the point of saving the completed file, or, of putting up with the nuisance of the pop-up box?

Thanks,

[mdmackillop]

Hi Ron,
Lost me here! Can you post the code which opens and changes the text file.
If you are if effect replacing an existing text file with data from excel then delete the file using the Kill command then recreate it with the new data.

[RonMcK]

Malcolm,

My apologies, I didn't mean to confuse you. The easiest way to explain my "problem" is to show you my "main" Sub and 2 of my Functions, one each for opening the text file and then, eventually, saving it as a XLS file.

I want to maintain the original text file, so, I have no need to kill it.

The question I was trying to ask is does ActiveWorkbook.SaveAs have an Overwrite option? From what I've read so far in Help is that it does not.

My thought was to save the imported/converted source data as an XLS file, early in the process, as a safeguard. However, the process is not that time-consuming that it would be a burden to re-run the whole process if it failed before the file was saved.

Does this make any more sense with the additional information ?

Thanks,

Ron


Sub Process_INFO_TXT()
MyFile = OpenFile()
Call ParseFile(MyFile, MyFilename, SrcExten)
Success = Open_Src_File(MyFilename, SrcExten)
' Application.ScreenUpdating = False
GetGrade Grade
Call GetOutFilename(MyFilename, OutExten, MyFileFormat, MyOutFilename)
Success = Save_OutFile(MyOutFilename, OutExten, MyFileFormat, True)
HideMyColumns
InsertMyCols
HeadUpCols
StartCell = "C2"
Assign_Nums
Success = FormatSheet(LastRow, LastCol)
If Not Success Then Stop
StartCell = "S2"
Separate_Stds
SortTable LastRow
Final_Formatting LastRow, LastCol
Success = Save_OutFile(MyOutFilename, OutExten, MyFileFormat, False)
If Not Success Then Stop
' Application.ScreenUpdating = True
End Sub


I've no idea wny VBA tags is inserting a pair of tags about 3 or 4 lines into the first Sub.

And are my Functions for Opening the source file and for saving the source as an XLS file after I'm done manipulating the data.


Public Function Open_Src_File(MyFilename As String, SrcExten As String) As Boolean
Open_Src_File = True
On Error GoTo File_Open_Error
Workbooks.OpenText Filename:=MyFilename & "." & SrcExten, _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True
On Error GoTo 0
Exit Function
File_Open_Error:
Open_Src_File = False
' End Function
End Function

Public Function Save_OutFile(MyOutFilename As String, MyExten As String, MyFileFormat As Long, Overwrite_OutFile As Boolean) As Boolean
Save_OutFile = True
On Error GoTo File_Save_Error
ActiveWorkbook.SaveAs Filename:=MyOutFilename, FileFormat:=56, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False, AddToMru:=True
On Error GoTo 0
Exit Function
File_Save_Error:
Save_OutFile = False
On Error GoTo 0
End Function

[mdmackillop]

Will SaveCopyAs meet your requirements?

[RonMcK]

Will SaveCopyAs meet your requirements?

By jove, that'll do it.

As I crawled into bed, last night, I had the realization that I could use Save (as in ActiveWorkbook.Save) which should work since activeworkbook has not changed from MyOutFilename. However, SaveCopyAs appeals to me because it takes the filename so there is no misunderstanding which file it is that I want to save.

Seems to me that I still need to use SaveAs for the first save so I can set FileFormat. But the 2nd save will be with SaveCopyAs.

Thanks,

[RonMcK]

Malcolm,

No joy! Sadly, I discovered that wb.SaveCopyAs Filename:=MyFilename returns a runtime error 1004 unable to access G4_INFO.xls.

So, I'm just using wb.Save and that seems to be doing the trick.

NEW QUESTION:

In addition to my main Sub I have a whole passel of supporting Subs and Functions. When I look at the Macro dialog (after clicking Macros icon), they all are listed. Is there a way that I can suppress all but the main Sub so users are not confused and they only see "Process_INFO_TXT" ?

Thanks,

[mdmackillop]

Precede them with "Private"

[RonMcK]

Got it. Thanks. I also removed the extra modules that are holding fragments of code. Macro dialog looks the way I want it to.

Thanks for all your assistance and patience,

[BrianMH]

I know your past this but


Application.DisplayAlerts = False
ActiveWorkbook.SaveAs "C:\workbook.xls"
Application.DisplayAlerts = True


should let you skip the overwrite warning.

[RonMcK]

BrianMH,

Thanks for reminding me that I had completely forgotten this way to keep pop-ups from puzzling the user. I've added it into my code. Saves having to include a screen shot in my user doc explaining why they don't need to work when M$ says there may be a problem.

Cheers,