How To Allow Users To Add / Delete Rows / Columns In Sheets With Macros

Mar 11, 2014

I have a sheet with macros that allows users to fill in data from columns F10 to O10 from row 10 to 54.

Column P will then make some computations based on the inputs from col F to O.
Row 55 will also make some computation based on inputs from row 10 to 54.

How to I modify the code below to allow users to either add/delete columns and rows such that it does not fowl up the computations?

The computation does not have to be at column P or at row 55, they can be dynamic and change depending on how many rows and columns the users delete/add.

A little background on what the code does. From Column F to O, the code UNLOCKS the columns only if user inputs date in Cell 3 of that particular column.

Example. Column F is locked from F10 to infinity at the start. Only when user inputs date into F3 does the cells F10:F54 is unlocked for user to input data.

This goes on until column O. I want users to be able to add more columns, or delete ones they don't need.

View 2 Replies


How To Create Macros Buttons To Add Or Delete Columns And Rows

May 21, 2014

So I'm trying my hand at creating VBA buttons and functions that do the following.

Button 1 :
to add a column with every click. The button will create one extra column. So by design there will be at least 1 column. Example if the button is clicked twice it will create 3 columns. I just need a buffer col. (contains no data)

Button 2 :
to delete empty columns leaving only one empty buffer column. That is this button will purge empty columns except the buffer column. OR if ALL the columns are filled, Button2 will create the buffer column.

Eg. I click Button 1 three times. It creates four columns. I only fill two columns. When Button 2 is clicked it will delete all the empty colums except the buffer column. OR I fill in all four columns, there is no buffer column, when I click Button2 it will insert the buffer col.

Button 3 :
to add a row with every click. The button will create one extra row. So by design there will be at least 1 row. Example if the button is clicked twice it will create 3 rows. I just need a buffer row. (contains no data)

Button 4 :
to delete empty row leaving only one empty buffer row. That is this button will purge empty row except the buffer row. OR if ALL the columns are filled, Button4 will create the buffer column.

Eg. I click Button 3 three times. It creates four rows. I only fill two rows. When Button 4 is clicked it will delete all the empty rows except the buffer rows. OR I fill in all four rows, there is no buffer rows, when I click Button4 it will insert the buffer row.

In the sample file there are six columns filled with data. The 7th col is empty(the buffer column). The column after that calculates the average of data in filled columns. There are eight rows with data and the ninth row is the empty buffer row.

The "add" buttons must be able copy the format/formula of the column/rows BEFORE the buffer colums/rows and insert it before the buffer column/row.

View 3 Replies View Related

Hiding Chart Sheets If Users Disables Macros

May 10, 2008

I am using Reafidy nice bit of code to hide worksheets if the user disables macros. The problem, though, is that it doesn't work for Chart Worksheets. How should I adjust this to accommodate charts?

Private Sub Workbook_BeforeClose(Cancel As Boolean)
bIsClosing = True
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim wsArray() As Variant
Dim iCnt As Integer
Application. ScreenUpdating = 0

Splash.Visible = True

View 4 Replies View Related

Allow Users To Insert / Delete Rows On A Protected Worksheet?

Apr 5, 2014

I have a worksheet that I have some "locked" cells on. I also have protected the worksheet but when I check to allow users to insert rows and to delete rows and then save it, when I re-open it I can't insert rows or delete rows???

I am using some VBA to allow for the collapseing of rows while maintaining protection and that works perfectly. I want users to be able to insert and delete rows while maintaining protection. I thought a simple check in the protection was good enough, but for some reason it isn't.

View 2 Replies View Related

Restrict Editing To Certain Columns/Rows And Allow Some To Be Edited By Users

Oct 29, 2007

how do I restrict editing to certain columns/Rows and allow some to be edited by users?

View 2 Replies View Related

Using Macros To Delete Cells And Rows Based On Specific Criteria?

May 7, 2012

Each week I run a report that produces over 2,000 rows. I have to manually go through and delete things, by the time I am done, there may only be 320 rows left. I would apply a recorded macro, but the report is not consistent each week. There may be more or fewer rows depending on the week.

I would like to be able to run a macro that looks in column B for a specific value and if it contains that specific value, I would like the cells to the right of it to have their contents cleared.

Next, in column C, I have...

Employee2's Name
Employee2's Number


I would like to be able to delete the rows, starting from Employee 2 to just before the second SUB TOTAL.

So, all I would have left in column C is


View 9 Replies View Related

How Do You Hide Macros From Users

Jan 30, 2009

I have created some macros and assigned shortcut keys. The workbook is shared by other users. I want to keep these macros from being accessible to other users. I know that they can go to ToolsMacros and display the names of macros and run them. How can I prevent them from accessing the ToolsMacros? Or can I assign password to individual macros?

If I put the macros in individual sheets as Private, they don't show up on the ToolsMacros list, but then I can't assign shortcut keys.

View 11 Replies View Related

Prompt Users To Activate Macros

Mar 27, 2008

I update an Excel file daily and save it to a server on a network. Other network users access this file from their own computers. I have a macro created that opens a dialog box upon opening that shows users know of anything important.

Since employees in the office have Excel set to Paranoid mode or something like that, they are never even prompted that macros exist and are intended to run and that they should choose to allow them to run or not.

I would like to approach the problem in a way other than asking all employees to activate all macros or to lower their Excel security settings at all times or only when opening this single document. That would be considered too inconvenient.

View 8 Replies View Related

Encourage Users To Enable Macros

Jun 14, 2008

I have been looking at Dave's Script found in this post Determining if macros are enabled It works just like i want it too. But when I try and use it in my workbook with differnt sheet names it doesnt work. I have even tryed to rename the sheets in the example file and it stops working. My sheets are as follows:

Sheet1 (JI)
Sheet2 (GC)
Sheet3 (ET)
Sheet4 (SV)
Sheet5 (JB)
Sheet6 (P)
Sheet8 (Info Sheet) <----This is where the Notice to turn on Marcos is
Sheet9 (D)

I have the following code in Module2

Public bIsClosing As Boolean
Dim wsSheet As Worksheet
Sub HideAll()
Application. ScreenUpdating = False
For Each wsSheet In ThisWorkbook.Worksheets
If wsSheet.CodeName = "Sheet8" Then
wsSheet.Visible = xlSheetVisible
wsSheet.Visible = xlSheetVeryHidden
End If
Next wsSheet
Application.ScreenUpdating = True...................

View 2 Replies View Related

Delete & Merge Columns,Delete Rows With Filter, Etc

Jul 15, 2009

1. Remove J,K,N,A Columns,

2. In the last O (TIMESTAMP) column, the date is 14-Jul-09 format change it to 07/14/2009 (this format mm/dd/yyy

3.Filter L column (VAL_INLAKH) Remove all rows from whole sheet which has 0 value

4. Column C (EXPIRY_DT) date format is 24-Sep-09 , "dd-Sep-09" change to "Sep" only

respectively )

View 3 Replies View Related

Share With Multiple Users Allowing Macros

Oct 9, 2008

I have a workbook that I would like to share and allow multiple users to add and make changes at the same time. However, the workbook is full of macros that really need to run. Is this possible? The workbook is written in excel 2003.

If this is not possible then is it possible to share the workbook in a similar fashion but not allow any other users to make changes, but update their workbook with the changes that I make?

View 2 Replies View Related

Moving Data From Rows To Columns And Delete Repeated Rows

Apr 16, 2014

removing duplicate rows and move other data frm rows to columns.xlsx.

I am attaching a sample excel sheet showing what I need to do.In the first tab, I have a list that includes duplicate rows (first column only). I want to remove those duplicate rows but I don't want to lose the data in the following columns which can be unique or duplicates as well.

see the desired result tab in the sheet to get an idea of what I am looking for as the end result.

Keep in mind that the actual source file I am working with could have up to 50000 row, and the expected results could be around 2000 rows. So nothing can be done manually.

View 5 Replies View Related

Delete Rows Of Other Sheets Also

Jun 23, 2007

I have a WB where the first sheet is "Introduction", second is "Master Data" wherein the name of players with some details are kept.

After that some sheets are there which are linked to "Master Data" ie say Sheet2,sheet3,Sheet5 (sheet 4 is not linked to "Master data" but appears after sheet3)

I want that if a record , say record 3 at row 5 is deleted also delete that record 3 in other linked sheets ie Sheet2,sheet3,Sheet5 The positions of the records differ from sheet to sheet ie if record 3 is at row 5 of master sheet it coulsd be on row 8 of sheet2, at row 10 of sheet3 & at row 6 of sheet 5. All the name of the person are linked to sheets with =masterdata!B5 like this.

View 9 Replies View Related

Delete Rows On Multiple Sheets Below

Apr 22, 2007

One more short macro code I would like to have.

I would like to delete rows on all worksheets in a workbook below where the value "Total" exists in column B (same on all sheets).

View 9 Replies View Related

Delete Columns From All Sheets Based On Condition In X Workbooks

Mar 28, 2008

I am sure this is a very simple questions. I am using the below code to work on the worksheets on a workbook called "MF BANK EXPOSURE SUMMARY.xls" so far the code that I am using is:

Sub Commandbutton2()
Dim iCol As Long
Dim Isheet As Long
Dim Item As Worksheet
For Each Item In MFBANK.Worksheets
With Item. Range("A1:T65536")
For iCol = .Column + .Columns.Count - 1 To 1 Step -1
If IsEmpty(.Cells(65536, iCol)) And IsEmpty(.Cells(1, iCol)) Then
If .Cells(65536, iCol).End(xlUp).Row = 1 Then .Columns(iCol).Delete
End If

Next iCol
End With
Next Item

End Sub

I would like to extend the code so that it works on the worksheets of two workbooks at the same time the one being the "MF BANK EXPOSURE SUMMARY.xls" and the other being "MF CP EXPOSURE SUMMARY.xls" .

View 3 Replies View Related

Delete The Columns First Then Rows Will Not Delete

May 22, 2008

Is there a limit on the number of rows and columns that can be deleted in a macro on Excel 2003? I am trying to create a macro that, amoung other things, delets 1119 rows and 54 columns. If I delete the columns first, the rows will not delete. If I delete the columns first, the rows will not delete.

View 12 Replies View Related

Delete Rows Based On Value In Multiple Sheets?

Apr 2, 2014

I have the following code to delete rows based on the value "No" in cloumn "L". This code should loop through all sheets and delete the corresponding rows.


The code works perfectly but with one little issue. It fails in the first run always with the following error message.

VBA error.png

I need to comment out the line

[Code] ....

continue it with a breakpoint to "End With", remove the comment. Then it loops all worksheets and deletes the rows.

There is no protection or similiar on any sheet.

View 1 Replies View Related

Macro Button To Add / Delete Rows Across Multiple Sheets

Jul 24, 2014

I've been piecing a macro together from different sites and have run into a couple issues I can't find resolutions to. I have a excel doc for tracking paint emissions per week. I would like to have a button on my master tab that will add a row and fill the formatting from the line above across all 60 some sheets. Ideally I would like the same button to first prompt if you want to add or delete rows. When adding rows I would like it to add it above the button (there's 3 buttons and for 3 different sections) and if deleting ask which row to delete. If it can't be done in one button that's fine, I can live with that. The big thing I need is for it to replicate across all of the excel sheets. I've attached a copy of the document

View 1 Replies View Related

How To Delete Rows With Strikethrough On Column A Among Multiple Sheets

Nov 24, 2013

I have a workbook with multiple sheets. I want to use VBA macro to delete the row where the first column is strikethrough. I try to the follow VBA code but it doesn't work.

Sub Macro1()
Dim cell As Range
Dim delRange As Range


View 3 Replies View Related

Add Or Delete Rows From Multiple Sheets Based Table Range

Aug 14, 2009

I have a need to add or delete rows from a number of known sheet names using a table of variables on another sheet that tell me the start row of the sheet I need to go to and the number of rows I need to either add (ie copy rows and paste / insert these) or delete (delete rows).

There are multiple blocks of data I mey need to amend on each sheet and the values in my table of variables will change on each iteration (ie if I delete rows from the first block on a sheet, the start row for the 2nd block I need to amend will need to be updated in the table of variables before I can edit the 2nd block on that sheet).

I have been able to get the process to work for a single instance (ie one sheet and amendments to the first block of that sheet) but I can't figure out how to create the loop to elegantly move to the next set of variables and repeat the process for the 2nd, 3rd, 4th block etc on the first sheet and then move to the 2nd sheet to repeat the process etc.

Public Sub EditCurrentBlock()
Dim rowcount As Integer
Dim startrow As Integer
Dim endrow As Integer
Dim rowcountBal As Integer
'Dim selSheet As Worksheet (tried to use this to nominate the sheet variable but
' had problems so scrapped it)

'Reconfigure the GP Revenue block.................

View 2 Replies View Related

Delete Rows With Columns Not Containing (MC)

Mar 29, 2014

I am attempting to delete rows with columns not containing "MC". In the example below, the result should be 3 rows remaining (1,2 and 5):






Instead, all I get are results with Y starting in MC, in this case the last row, all the other rows are deleted. I need to get all rows that have MC in three columns (Y, Z or AA) and delete the ones that do not. In this case, rows 3 & 4.

Below is the code I have been using:

Sub Del_Rows()
Application.ScreenUpdating = False
With Range("Y1:AA1", Range("Y" & Rows.Count).End(xlUp))
.AutoFilter Field:=1, Criteria1:="*MC*"
End With
Application.ScreenUpdating = True
End Sub

Seems simple enough but this newbie chump is stumped why it is not reading the other columns

View 4 Replies View Related

Delete Columns, Not Rows

Jan 12, 2007

I tried to modify this code for my next problem: to delete entire columns where the cell in row 2 is empty, but it doesnt work. The code I use is:

Sub test2()
'This code will create a copy of the current sheet, and retain only the rows
'that have the value '1' in column B.

ActiveSheet.Copy Before:=ActiveSheet
Application. ScreenUpdating = False
On Error Resume Next
Dim ir As Long, mrows As Long, lastcell As Range
Set lastcell = Cells.SpecialCells(xlLastCell)
mrows = lastcell.Row
'Note rows are deleted from the bottom going up
For ir = mrows To 1 Step -1
If Len(Trim(Range("b" & ir).Value)) = 0 Then
Rows(ir).Delete Shift:=xlUp
End If
Application.ScreenUpdating = True
End Sub

The ideal solution would be not a second macro but one that does firstly delete the rows and then the columns.

View 8 Replies View Related

Delete Rows, But Not Across All Columns

Mar 4, 2008

Is there a way to delete x # of rows in columns b, c, d, g, j, m, p & s.

I regularly enter data in these rows for comparisons and then need to delete the data with out deleting the formulas in e, h, k, n, q & t.

A macro or one key then delete would be great.

View 4 Replies View Related

Macro To Split Data Into Three Different Sheets / Tabs - Delete Blank Rows

Jul 30, 2013

I'm pasting a lot of data into a spreadsheet and then using a macro to split the data into three different sheets/tabs. I'm doing this by simply copying entire columns. Each sheet/tab has a headings row and autofilters added so that my boss can filter on certain manufacturers in one of the columns.

The problem is that when he selects a manufacturer, at the bottom left corner of the screen it shows the number of records, which at the moment will be something like "6 of 65211".

The actual sheet only has a few hundred rows (not 65211). So how do I delete all the blank rows beneath my data using a macro or vba code?

As an extra point, I'd like to use column B to check for blank rows (not column A).

View 6 Replies View Related

Delete All Rows That Have Blanks In Both Columns?

Aug 23, 2012

I am trying to delete all the rows that have blanks in both columns "D" and "F".

With Sheets("2012")
For k = 6 To 1000
If (.Range("D:" & k) = "" & .Range("F:" & k) = "") Then


View 4 Replies View Related

Delete Rows/columns Across All Worksheets

Jun 24, 2009

I have a single workbook with 55 worksheets. I need to delete rows #1-14, then delete columns A & B on all worksheets. The macro below seems to be running in a loop which appears to delete rows 1-14 and columns A & B until there is no more data left on the active sheet only. It does not run on any of the other sheets - even if I select all sheets before running the macro.

View 4 Replies View Related

Delete Rows With Strings In Only One Of Two Columns

May 10, 2012

I have two columns. In the first column, there is in every cell some text. In the second column, there are some blank cells. It looks like this

textCOL1 textCOL2
text2COL1 text2COL2
text3COL1 text3COL2
text6COL1 text6COL2

How do I remove the rows which have text only in the first column?

The output should be:

textCOL1 textCOL2
text2COL1 text2COL2
text3COL1 text3COL2
text6COL1 text6COL2

View 4 Replies View Related

Delete Rows By Criteria In 4 Columns

Jul 14, 2004

I have a large spreadsheet that is 65000 rows and colomns A thru W.
I need a macro to delete rows that if Date and Account and Type are
equal and the net of Quantity is zero delete those rows.

Col A = Date
Col C = Account
Col D = Type
Col J = Quantity

What the macro would do is if an account had 250 rows of activity on
say 7/7/2004 (Date) and same type of account and the net of all
Quantity is equal to zero delete those rows.

View 9 Replies View Related

Delete Empty Rows & Columns

Nov 17, 2006

I would like to delete all unwanted rows and colomns. I know this can be done but Old Age is catching the grey matter

I want to delete all except the following ranges, Range("A1:H27").Select

View 4 Replies View Related

Delete Rows Based On Two Columns

Dec 22, 2006

I have a spreadsheet that in column A has either the letters "C", "LP", or is blank.

In Column L it has either numbers 1 through 100, or the letter "D".

What I need it to do is to check and see if the data in column L is "D" and in the same row column A is blank, then delete that row.

Also, if the data in Column L is "D" and the Data in column A is either "C", or "LP", then I need it to change the "D" to "1".

It might be easier to do it separately because once you run the part that deletes the rows that don’t have a “C” or “LP” then I could probably just do one that says if column L = “D” then change to “1”.

Lets see if I can simplify this for you.

If Column L = “D” and Column A = BLANK then Delete Row
(this would delete all “D’s” that don’t have the “C” or “LP”)


If Column L = “D” then change to “1”

View 9 Replies View Related

Copyrights 2005-15, All rights reserved