Row Insertion Macro |
Row Insertion Macro - Excel |
|
Quote:
Code:
Sub Input_Lines_1() ' ' Rows("4:4").Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Rows("11:11").Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Rows("18:18").Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Rows("25:25").Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Rows("32:32").Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Rows("39:39").Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Rows("46:46").Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Rows("53:53").Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Rows("60:60").Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Rows("67:67").Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove End Sub
Similar Topics
Until now I have been able to find all my answers through searches. As a VBA novice, it has been very helpful. I am stumped on this one, however. I am trying to autofill from the selected cell in Column C down. I would like it to stop at the last cell with data in Column B. This is the code I have so far:
Range("C2").Select
Selection.AutoFill Destination:=Range(Selection, Selection.End(xlDown))
This does the autofill, but doesn't stop at the last cell with data in Column B.
In the past I have use this code to acheive similar results:
Dim endRow As Long
endRow = Cells(Rows.Count, "B").End(xlUp).Row
Range("C2").AutoFill Destination:=Range("C2:C" & endRow)
The problem with this code is that I will not always be starting in "C2". I need code that uses whatever the selected cell is.
All help is appreciated. Thanks!
Code:
Range("Activecell.End(xlDown)", "Active.End(xlToRight)").Select
This is definately a problem of not knowing the right jargon to do so.
Could someone please assist?
Thanks in advance!
final
3 & 4 shifts = 15% Basic
2 shifts = 10% Basic
If on leave for 21 working days or more, no shift allowance
If on leave for less than 21 working days and on 3 or 4 shifts, shift allowance prorated as (Basic*15%)/21*(diff. btw 21 and no of days leave observed)
If on leave for less than 21 working days and on 2 shifts, shift allowance prorated as (Basic*10%)/21*(diff. btw 21 and no of days leave observed)
The formular I used in the attached worked but the only problem is that I had to sort by no. of shifts and adjust the formular first. I need A formular that does not require sorting. I tried to combine IF, AND, OR formulae but I got error. Please assist me sort this out. Thanks a bunch.
I need to insert 5 blank rows repeatedly between every existing data rows
for approximately 300 rows.
If I go about doing the repeat short-cut "Control+Y", it just repeats
inserting ONE row only between the consecutive data row.
Is there some command, which helps me highlight all the rows & allows me to
insert 5 blank rows between every consecutive existing data row?
Thanks in advance for your kind advice.
Hi all! Can anyone tell me the shortcut for Delete, Shift cells up? I have checked through Google but could not find the shortcut.
Thanks!
Thanks in advance.
Mike
Example: Existing data 1000UG01
Needs to be 1000UG.01
Existing cells are all the same length and the "." needs to be inserted in the same place.
Thanks in advance for any help.
Then Paste My answer in "AM1"
This is what I have so far and for some reason is not working.
Code:
myRange = ActiveSheet.Range("H2", Range("H2").End(xlDown)) Range("AM1") = WorksheetFunction.Sum(myRange)
Any Ideas?
I have the below code which deletes all items from a listbox and my excel sheet which is the source for populating that listbox. I am using a option button style for my listbox and the selection style as single .i.e. you can select only one item at one go in the lisbox. I want my macro to delete the selected item from my worksheet .i.e. it's entire row so that it doesn't reflect in my lisbox any more. Below is my code :
Code:
Private Sub CommandButton2_Click() 'REMOVE SELECTION Dim I As Long With ListBox1 For I = .ListCount - 1 To 0 Step -1 If .Selected(I) Then .RemoveItem I Sheets("URL List").Rows(I + 2).EntireRow.Delete End If Next I End With End Sub
Thanks a lot for your help in advance.
Currently we use a totally manual spreadsheet which looks like a Rubik's Cube and does not offer any form of automation in order to speed up entry adn ensure no doubling-up or missing shifts.
Code:
Sub Mail() Dim OutApp As Object Dim OutMail As Object Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) On Error Resume Next With OutMail .To = "cbelcher@jpfryelaw.com" .CC = "" .BCC = "" .Subject = "Burden Report" .Body = ActiveSheet.Range("A1:D12") .Attachments.Add ("C:\Documents and Settings\cbelcher\Desktop\Burden Report.xls") End With On Error GoTo 0 Set OutMail = Nothing Set OutApp = Nothing Set objMsg = Nothing End Sub
I have entered the following formula in to a cell: =IF(S3<R3,"DOWN","UP") it works however i want to replace the word Down and up with the relevant arrows symbol. How do i do this?
Thanks
Im working with a large data spreadsheet that has Yes and No answers in different columns. There are blanks within the selection too.
I need to calculate only the Yes.
I have tried using the filters in the Pivot Table field list and they are not working.
So now i'm trying to add a COUNTIF formula in the Calculated Fields section.
The formula I'm using is =Countif('Time limit extension'="yes") and i get the answer "too few."
What should I be using in the formula to get the calculated field to work? I have search the whole forum and I haven't found an answer that can help me.
Please HELP!!!
Random question i have a large Excel Workbook (which is protected) and has over five sheets on it - however one sheet has randomly decided not to scroll... yes i know sounds random! If i use the cursor and down arrows the selection just disappears off the screen.
The page will scroll if i filter by one field, but not if i select (All) for all filters.
All other worksheets scroll fine.
Any suggestions?
Thanks in advance.
Cheers Kaite
I am using below code to Select the Visible rows in the target range:
Code:
Range("A:p").SpecialCells(xlCellTypeVisible).Select
Problems in this code a
1) after applying the filter, while selecting the data it is selecting all the rows in given range till last row on the workbook. I need this to select the the data only till the last used row in the given range.
2) It is not possible to provide the address of the first row after we apply the filter since the first row address may change depending on the values in the table.
E.g. 1st time when I am running the macro the first row in the visible filtered data is starting at Cell address A4 and next time when I will run the macro it may be A6
3) The Code is also selecting the 1st row which is a header row. How can we exclude it from selection.
Some one please revert with the solution.
Thanks in advance.