score:0
while i do not understand clearly what you want to do, i think the bellow code will help.
please adjust it to your needs.
sub add_some_fancy_rows()
dim resizer as integer
dim a as variant
a = inputbox("please enter the number of rows you want to autofill", "let me do it for you") 'first we ask for user input
on error goto notvalid 'we add an error handler, so if the user would enter text like "seven", the sub will exit with a message
resizer = cint(a) 'we store the input in a variable which has to be an integer, if the user enters text it will couse an error so we jump to the end
if resizer < 2 then goto notvalid 'we also check if the number is higher than 1, othervise it couses error, or copies the 8th row to the 9th
on error goto 0 'we reset the error handling so we can see if something else goes wrong.
thisworkbook.sheets("sheet1").visible = true
thisworkbook.sheets("sheet1").select
thisworkbook.sheets("sheet1").rows(9 + 1).entirerow.insert shift:=xldown 'add a new row under the 9th row/above the 10th row
thisworkbook.sheets("sheet1").rows(9).resize(resizer).filldown
exit sub 'we exit the sub before the error message.
notvalid: 'in case of error we jump here
msgbox "please enter a number which is 2 or higher"
end sub
i hope it helps
Source: stackoverflow.com
Related Query
- Insert rows based on the user input (textbox value)
- VBA Excel- Get Cell value and associated rows into another worksheet based on User Input
- How to loop through the rows of a continuous form in microsoft access and insert value into unbound textbox using vba?
- Excel VBA to insert rows based on cell value and copy columns down based on that number
- VBA Code for Making Entire Rows Bold based on a Cell value in the Query table
- Number of rows based on the cell value (loop with copy)
- Table and form based off a different table - see if a common record exists in the table and insert a "yes" or "no" value in the form
- Changing a positive numeric cell input into a negative numeric value in the same cell based on criteria in another cell
- Cut and paste a specific row based on user input in the same excel worksheet
- Insert rows based on cell value and swap cells
- VBA_Populate userform data by insert rows in between cells based on the Matching text
- VBA - to Loop rows and insert value based on condition
- Loop through the range and then sheets and delete rows based on cell value
- VBA Access - Adding a New Record in Table Based on User Input and Auto Completing the Rest
- Input box value insert in the next empty cell in row to the right
- Setting an entire rows value based on the cell value in column A?
- How to insert multiple rows based on criteria in Column from the top of table
- Send Textbox Data to a specific row/cell in a Worksheet based on the Value of a ComboBox
- Select a specific number of rows (below the header row) based on value in another cell
- VBA Finding the next column based on an input value
- Excel VBA loop through column and insert rows based on cell value
- My VBA code for a textbox does not include the final input in the cell value
- Excel VBA insert rows under the column if adjacent cell value matches column names
- Copying a value from range "H" after inserting rows based on the range "F" value
- How to Insert # of Rows based on Value Criteria in Column
- Copying rows to a wksheet based on the value in a specific column isn't applying to my whole spreadsheet
- If user skips rows to input a numeric value, force user to type in data in the following available row
- Insert number of rows based on value in adjacent cell
- Google Scripts or VBA - Insert rows upon entering input value (Excel)
- macro to insert blank rows based on column value
More Query from same tag
- Excel VBA ADODB Late Binding error
- VBA : vbYesNo displays a button with OK and nothing else
- Excel VBA create report with grouped values
- VBA create CSV from sheet named after Workbook
- VBA Excel - Copy Rows to Another Workbook Sheet with conditions
- Refer column as a number in activecell.formula function in VBA
- Rearrange columns on all worksheets
- Access sending emails via SMTP
- If cell is numeric only move to opposite cell
- Word 2007 VBA: ActiveDocument.CustomXMLParts
- Terminate after If [VBA]
- Strange error with Selection object and Userform
- Automating certain repetive actions in IE based applications via a c# tool?
- Match function for date matchup
- Macro to highlight rows of data does not run in Excel workbook
- Loop copying in the column rows
- Can you change the criterial for the AutoFilter method?
- How to loop through cells in a section of columns within a range?
- Insert image path into table in access by vba
- VBA copy range from another sheet
- Incorrect target range in Worksheet_Change event
- Cropping a fixed area from a picture in VBA
- Populating VBA array with results of ODBC sql query
- How To create a report using data from Excel in Word Template
- mapping certain values from one worksheet to another using loop and array
- "Automation error: The object invoked has disconnected from its clients." when calling from another sub
- Using Target.Column every nth column
- Append Query - Multiple Tables into Single Table
- Open Excel file from batch script and return message to batch script after closing it again
- Why do some of my SQL queries work in a VBA script and some don't?