score:36
Accepted answer
if you want to just shift everything down you can use:
rows(1).insert shift:=xlshiftdown
similarly to shift everything over:
columns(1).insert shift:=xlshiftright
score:2
sub addrisk()
dim ractive as range
dim count_id_column as long
set ractive = activecell
application.screenupdating = false
with thisworkbook.sheets(1) 'change to "sheetname" or sheetindex
for i = 1 to .range("a1045783").end(xlup).row
if 'something' = 'something' then
.range("a" & i).entirerow.copy 'add thisworkbook.sheets(index_of_sheet) if you copy from another sheet
.range("a" & i).entirerow.insert shift:= xldown 'insert and shift down, can also use xlup
.range("a" & i + 1).entirerow.paste 'paste is all, all other defs are less.
'change i to move on to next row (will get + 1 end of iteration)
i = i + 1
end if
on error resume next
.specialcells(xlcelltypeconstants).clearcontents
on error goto 0
end with
next i
end with
application.cutcopymode = false
application.screenupdating = true 're-enable screen updates
end sub
Source: stackoverflow.com
Related Query
- EXCEL VBA, inserting blank row and shifting cells
- Excel VBA - Pass a Row of Cell Values to an Array and then Paste that Array to a Relative Reference of Cells
- Excel VBA Copy block of cells with variable row and column number
- Selecting a range of cells and inserting a blank row for every number that the sequence of those cells is off by
- Excel VBA delete entire row if both columns B and C are blank
- How do I use Find and Loop in VBA for Excel to identify, delete, and insert blank row for values greater than 6?
- Excel VBA - choose workbook to copy from and paste into existing workbook on next blank row
- Excel VBA looping through empty cells and retrieving row values
- Inserting text to blank row using vba macro in excel
- Excel VBA code to copy a range of cells in a row and paste in a different sheet but same row/range, when the value on 1st cell changes
- Excel vba select non blank cells in column A and create array offset from B
- Excel VBA - find and delete blank cells in a row-range
- excel macro: Delete row with 1 or more blank cells and shift data up
- Excel VBA - Iterate through a Column of Cells and then Cells of a Row
- Excel VBA inquiry regarding find a specific word, inserting a row, and pasting into said row
- First the next and previous cells with data in a row Excel VBA
- Inserting a blank row after each row in vba for excel
- Identifying and highlighting a blank row in Excel VBA
- VBA Excel How to delete filter blank rows (but not entire row) and shift cells up
- Excel VBA - Find all cells with value and delete the entire row if it exist
- EXCEL VBA Find All Blank Cells in Columns C:O and then color them red
- excel VBA function to select the first row in a column and it will will select all cells below it and make it range
- Excel VBA Find all cells containing text (partial match) and return entire row
- Copy non-contiguous cells and paste in a row retaining number format Excel VBA
- Cut entire row where specific cell contains a keyword and paste it into a new worksheet while shifting remaining cells up to fill in the blank spaces
- Excel VBA trying to insert a blank row in cells A2:E2
- Inserting formula into last row and autofilling until the last column Excel VBA
- Find last three cells in a row cut and paste to last three columns excel vba
- VBA Excel - Transform Column value To another Column along with inserting blank row
- Excel VBA Conditional Formatting Blank Cells in Row
More Query from same tag
- Update Query takes only one row as value - Access 2000?
- Using VBA to create a dynamic table in Access 2010
- VBA Word - Nested IF Function, A Macro Running Other Macros in all Documents in a Folder
- paste data into active cells
- Read content when an Excel workbook is closed using Excel VBA
- How to fill cell with a running value until I meet a new number and calculate the sum?
- Worksheets("Worksheet 1").Activate doesn't always work :-(
- Data copied is not pasting in destination workbook
- To apply 2 separate filters on 2 separate columns count the number of rows
- My VBS script is looking for file in wrong directory
- Determining All Styles Used in Endnotes
- Concurrency and connection issues to Access DB stored in SharePoint Library
- How do I close an excel file in the Workbook_Open event when I have multiple files open?
- Insert a picture using VBA, different size between Excel 2010 and Excel 2016
- Keep the same variable in different Active Sheets
- Selecting sheet to copy to based on cell value(Month name) from other sheet in other workbook
- Edit a .ppam PowerPoint add-in and save changes
- Applying a formula to multiple columns, across multiple sheets in Excel
- Vba-Excel: Export Excel Sheet to XML?
- Remove and count duplicates
- Dictionary is populated with an empty item after checking dictionary item in watch window
- Performing Form Validation with an Access 2003 ADP project
- Updating an array stored in a VBA dictionary
- VBA excel - How to 'saveas' file without preserving the first file
- Create SAFEARRAY of Strings in Python
- VBA - working with get/let for some reason property won't set
- Error 381 populating listbox from a sheet table
- Returning unique strings from two arrays of strings, concatenated in one string VBA
- DoEvents, Waiting, and Editing
- Replace 2 double quotes with 1 double quote with VBA