Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

VBA: Identify Specific Row based on Command Button Location

First post-- Hello all!
I am creating and deleting command buttons programmaticly. This part is a piece of cake. The trouble is that when I delete a button I also want to delete any rows the button is "touching". This WOULD be easy if the buttons weren't moving around, but the buttons may change location (for instance, after a row has been inserted somewhere, the button might be pushed down). So, if a given button spans roughly A3:B4, then I need to delete rows 3 & 4 when I delete the button. But how do I identify a specific row based on the button properties?


Post your answer or comment

comments powered by Disqus
Hello all,

First post. Thank you in advance for your help. I appreciate the effort, and forums like this are really great.

I am looking to add some functionality to a workbook I am working on, and hopeful that someone may be able to help.

I am looking to identify multiple rows, across multiple sheets, and paste those into a new sheet. The idea is to aggregate specific data into a central location.

I'd like the function to do the following:
1. Identify if column D has an 'x' in it
2. If 'x' is present, select and copy columns B through O for the specific row
3. Paste columns B through O for the row into the new sheet
4. Move on to the next worksheet and repeat
5. Continue until all worksheets have been "parsed" and aggregated

I found a couple macros that have moved me in the right direction, but I cannot seem to get the functionality to work quite right.

Any help would be truly great. If you do provide code, if you could provide comments on what each line does, that would be very helpful for me so that I can learn how it works, and make edits as needed.

Thanks again. Much much appreciated.

--Sam

I'm trying to create a log sheet that will show a set number of rows based on two 'input' cells. I found the following thread that could help me along, but I can't get it working quite right for what I want to do http://www.excelforum.com/excel-gene...hide-rows.html

Attached is my sheet, and the code that I think will get me most of the way to what I want to do is below:
Public Cntr, n, colIndex
Public TakeAction
Sub UnHideAllRows()
    Cells.Select
    Selection.EntireRow.Hidden = False
    Range("A6").Select
End Sub
Sub DynamicHiding()
    ReDim SheetsFound(0)
    For Each aSheet In ActiveWorkbook.Sheets
        If InStr(1, aSheet.Name, "(") = 0 Then
            Select Case TakeAction
                
            Case Is = 70
                HideRowsWhenZero
                'aSheet.PrintOut
            Case Is = 80
                aSheet.Activate
                UnHideAllRows
            End Select
        End If
    Next aSheet
    Sheets(1).Activate
End Sub

Sub HideRows()
BeginRow = 10
EndRow = 350
ChkCol = 8
Application.ScreenUpdating = False
    For RowCnt = BeginRow To EndRow
        If Cells(RowCnt, ChkCol).Value = 0 Then
        Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        End If
    Next RowCnt
Application.ScreenUpdating = True
End Sub

Sub UnhideRows()
    Application.ScreenUpdating = False
        Cells.Select
        Selection.EntireRow.Hidden = False
        Range("A1").Select
    Application.ScreenUpdating = True
End Sub
Taking a look at my file will help explain what i would like to do. On the far left are yellow boxes, with 11 rows beneath each. The yellow boxes are the # of products referenced in EG9. I want to have EG9 affect how many of the yellow boxes are visible along with the number of white boxes beneath each being determined by EG8. I'm not very proficient at VBA, so help is greatly appreciated.

Hi All,

I am trying to put together a code which could help me move rows based on a cell which can have variant cell value. What I'm trying to do is, move the sheet based on the value in the column A. Column A has the names of different person worked on a project.( This is a dummy file). What I want is, different sheet prepared for each persons who have worked on a project. And show the attributes against them.

Note: I want the header to be copied as well.

I'm attaching a sample excel file for reference. Please let me know if this helps. I have searched online and got similar questions but all of them had a predetermined sheets ( sheet already named before). But here, What I want is to create a sheet with the employee name as and when they are moved. Say , I have several rows for Robert - I want VBA to copy the rows that has Robert and copy it to a new sheet and name it as Robert. There are possibilities that there could be extra or new faces coming into the project. So we cannot have a pre determined sheet named after employees. Eagerly waiting for the responses.

Sample.xls

I am trying to automate the copy process of rows from one worksheet to
another based on specific data. I am running a DB query and returning the
data to my workbook. I then want to copy certain rows to another sheet based
on specific values in the first column. The data being returned is Date and
Time formatted as follows: mm/dd/yyyy 13:00 and is returned in ten minute
increments.
I want to automate copying rows from this worksheet to another worksheet
based on specific times in the first column. These values are not always in
the same rows.
Can I write a macro to copy the specific rows based on the time entered in
column one? If yes, what is the proper syntax?

Hi!

Just a little bit confuse on how to create a macro using if then else (i supposed this will solve the problem). I want to hide all the rows that have contents in a worksheet but shows only the specific rows based on the what i want to show (B2). Can someone help me on this? I have attached the sample file for your reference.

Thanks in advance!

I am trying to write a macro that will copy and paste a formula to a specific row based upon my input in a certain cell. Therefore, one time I might need to have the macro copy and paste to row 25 and the next time the macro might be required to paste the formula to row 50. I have attached the file including the basic macro. Any help would be greatly appreciated. Thank you.

I am trying to automate the copy process of rows from one worksheet to
another based on specific data. I am running a DB query and returning the
data to my workbook. I then want to copy certain rows to another sheet based
on specific values in the first column. The data being returned is Date and
Time formatted as follows: mm/dd/yyyy 13:00 and is returned in ten minute
increments.
I want to automate copying rows from this worksheet to another worksheet
based on specific times in the first column. These values are not always in
the same rows.
Can I write a macro to copy the specific rows based on the time entered in
column one? If yes, what is the proper syntax?

My macro currently looks like this:
Sub Day1()
'
' Day1 Macro
' Macro recorded 6/20/2006 by Matt Lance
'

'
Range("V7:V28").Select
Selection.Copy
ActiveWindow.SmallScroll ToRight:=-7
Range("D7").Select
ActiveSheet.Paste
End Sub

What I want to do is have it so that instead of selecting D7 to paste, I want it to select the same column that the command button is in and then the 7th row. This way I can have one macro used across several buttons instead of having a macro for every column I use.

Does anybody have any suggestions??

Hi,

I have a query in excel regarding the extraction of certain specific rows based on a criteria. It goes like this:

I have two sheets in one excel file. The first excel sheet has five columns: Name, number, date, type and reg - with approx 8000 rows.

The second excel sheet has just one column: 'Name' with around 160 rows.

My Requirement: I want to scroll through each value in the second sheet(which is a text), check whether that particular name is present under the Name column in sheet 1, and if it is present, copy the entire row to a new Sheet: Sheet 3.

I need to get this done for all the 160 records from sheet 2 and cross check it with all the 8000 records of sheet 1 and thus get the output.

Hope I have made myself clear.

Thanks.

Hi all, I am having some difficulty with conditional formatting. I am trained on 07 but am working on 03 so Im kind of lost.

Basically what I am trying to acheive is this. We are a construction company and we set pre-made concrete pieces, ie wall panels and parking structures, just to give a little background. Well when the pre made piece is shipped to the jobsite it may be damaged or made incorrectly. Our crew is responsible for making the repair to the piece in the field. Before doing so, the foreman of the crew lists the problem and sends a repair request to the engineers, which will in turn send a repair detail instructing them exactly how to correct the problem.

Im trying to track the status of each repair request sent, repair detail recieved, and when completed. So I have designed a spreadsheet for the foremen in the field to key in the data neccesary to show that status of each repair. Now, to the issue at hand, I want to highlight each specific row based on what data is entered. I want the row to turn yellow when a date is keyed in for when the repair request was sent(I have a cell made for each of these dates to be entered within the same row), then once the repair detail is recieved and the date is keyed in I want the row to turn orange. Finally when the repair has physically been made and the date is keyed in for that cell I want the row to then turn green.

So, with the date itself not being relevant, how do I highlight the entire row based on which value was entered last? I have searched and searched, and tried many different variations of formulas but I am having no luck. Can anybody help me? Sorry for the legnthy explanation.

I need assistance in writing vba to remove duplicate rows based on oldest date. I have a worksheet that is an update sheet. This sheet finds the current record with a number of columns that feed a form. The user will make necessary changes to the record and hit a command button which updates the record by finding the next empty column. I need the updated record to find the oldest record (which should only be one record) and delete it and keep the most current record.

The two columns that it needs to compare is "A" (a ZIP Code column) and "B" (the date column).

Your assistance is appreciated

Hello ozgrid faithful,

I have been researching a VBA solution to a common problem, moving row(s) to a new sheet based on column criteria. I have found several responses on this forum that deal with this issue, however the VBA responses are too customized to the individual poster, and the number of unique criteria is too large to use an advanced filter (repeatedly). I'm hoping someone could provide generic VBA coding to the problem, which I have illustrated below:

A data table has 'x' number of columns and 'y' number of rows. Column A has attribute data that will determine which row(s) are copied to a new (VBA created) sheet, named after the attribute. The workbook will start with 1 sheet, and end with multiple (creating a new one each time the Macro is run).

I understand that a Pivot Table would similarly summarize this information, however I need to be able to electronically distribute a confidential list specific to each attribute (AA can't see AB's, AB can't see AA's, etc). I'm not sure how the code is setup, but please ensure that it is generic enough where the number of rows and columns can fluctuate (though column A will always contain the determining attribute).

I have attached a sample document with the criteria described above. Your help is greatly appreciated.

The solution will have 5 tabs ["Sheet1", "AA", "AB", "AC", "AD"], each with 7 columns. "AA" and "AB" will have 2 rows, "AC" and "AD" will have 1 row.

Title pretty much sums it up.. I have been thumbing through other posts that are somewhat similar but cannot seem to figure out how to piece a code together that would accomplish what I need. The example will help describe what I need the best..

Basically just want to hide/unhide to seperate groups of rows based on the criteria of a specific cell. I hope some simple VBA could resolve this..

Thanks for your help.

So I've been creating a workbook that manages projects.

My problem is I want cell links to be created automatically when a new sheet is created.

When a user selects 'Add New Project' button the workbook makes a copy of a Project Template. I want the 'Project Dashboard' worksheet to have links to the new project template which will eventually show specific values in the next available row. Links such as (i.e = NewProjectSheet!A1 in cell B5 on the project dashboard)

(In the attachment, the 'Test' worksheet is resembles a 'new project template')

Test.xls

I specifically need help with VBA code to find the next available row based on the range (B5:F23) then create cell links in that new row.

Is this possible? If so can anyone give me a hand?

Many Thanks

Hi all,

I have been a long time follower of the forums, 1st time posting. Pretty noob at VBA. I have a sheet with a hierarchy of operating units in a column, with DirectX checkboxes next to each (used a VBA script to auto-create the checkboxes ).

The checkboxes go from D5:D147, so 142 checkboxes. They are named checkbox_D5, checkbox_D6 all the way to 147, so checkbox_[Column]&[Row].

Based on a True/False value in another column to the right (BA or column#53), I am hiding specific rows. Just to give a sense, if a checkbox for a Level 2 hierarchy is clicked, I have some formulas to determine which Level 3 rows will be shown by putting a TRUE in column BA. For sake a speed, I created these to only run for 30 rows per click (that's the most that would ever change on a checkbox click).

I did not know how to do it any cleaner, so I created 142 of these:


	VB:
	
Private Sub CheckBox_D5_Change() 
    Dim i As Long 
    For i = 5 To i + 30 
        If Cells(i, 53) = False Then 
            Rows(i).Hidden = True 
        Else: Rows(i).Hidden = False 
        End If 
    Next i 
End Sub 
 
 ' second one would be checkbox on the next row
Private Sub CheckBox_D6_Change() 
    Dim i As Long 
    For i = 6 To i + 30 
        If Cells(i, 53) = False Then 
            Rows(i).Hidden = True 
        Else: Rows(i).Hidden = False 
        End If 
    Next i 
End Sub 
 ' continued like that until D147

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
It works OK up to checkbox_D23 or 24, but excel seems to reduce the number of rows it goes through for each checkbox below that, with checkbox_D31 down doing nothing. I read somewhere about a 32 form item limit, wonder if I'm hitting that.. Ideas? Please? Cleaner way to do this vs. 142 instances of checkbox_XX_change() events?

Hey everyone, I have a simple problem that I can't fix.

I have a commandbutton sitting on a test sheet. When I click the button I want it to hide a block of 3 rows that sit 3 rows under the button. I am trying to use the TopLeftCell property to achieve this. Here is the code:


	VB:
	
 CommandButton1_Click() 
     
    Dim currcell As Integer 
    currcell = CommandButton1.TopLeftCell.Row 
    toprow = currcell + 3 
    bottomrow = currcell + 6 
    Range("toprow, bottomrow").Select 
    Selection.EntireRow.Hidden = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
For some reason I just cannot get the range part of the sub to work. I know it's probably syntax-related but I just can't work it out.

Thanks!

Hi,

I have a problem which needs immed solution else I will be thrown out of the company ...

In a workbook, there are three sheets ..

First sheet has 2 sections.. row no. 1 to 10 contains data like .. Name, emp id, team name, manager name , date of entry etc..

Section 2 of sheet 1, that is row no. 13 onwards contains something like this --

Request Type (choose from 1-4)

Field 1.1
Field 1.2
Field 1.3

Field 2.1
Field 2.2

Field 3.1

Field 4.1
Field 4.2
Field 4.3
Field 4.4

Sheet 2 contains entries like this..

Request Type 1

Record1
Record2
Record3
.....

Request Type 2
Record1
Record2

Request Type 3
Record1

Request Type 4
Record1
Record2
Record3
Record4

What I want is this --

When a user wants to create a new entry, he will enter his data in section 1 of sheet 1, then he selects a type of request from section 2 of sheet 1, ( I have a dropdown for this, so that part is set).. based on his selection, only the fields specific to that request type should be soon .. ( I dont know how this can be done , but may be by hiding specific rows based on a dropdown selection ?? ) ..

When the user is finished putting the data in respective fields of sheet 1, he clicks on a command button and that adds that records (along with data from the section 1) as a record under that request type heading in sheet 2... if there are records already there, the rows should be pushed down and the new record should be added on the top..

Hope I have made this as clear as possible.. please help ASAP !!!
_________________
- If you cant get it right in the first time.. Sky-Diving is not for you !!

I need some help with some VBA in Excel to find a particular cell value (i.e today's date) and then based on that criteria select, copy and paste a range of rows into another worksheet based on the criteria that they are in the past or future. My worksheet is sorted ASC by date and have read a number of posts simillar to this problem but I didn't solve on my own so don't know if my problem is lack of expertise in working with dates, or that all the threads I read had slightly different requirements.

I guess what might have been adding to my grief is that I specifically don't need any rows with today's date. Once I get this sorted out I will be adding logic to the 'what date is it now' criteria (i.e 28 days before and after today's date) and copy/pasting rows based on that. I think if some kind guru can help me with the above, I should be okay from there.

I have attached a sample spreadsheet and pray the question is clear and concise enough
Best Regards
Larry

Hi,

Iam currently using:
Code:
UserForm1.Frame7.Visible = Range("A3").Value = 1 Or Range("A3").Value = 2
UserForm1.Frame4.Visible = Range("A3").Value = 1
To enable (make visable) different frames based on a cell value.

I also need to do this but based on Command buttons but I only need to make them enabled or disabled. Is it possible to do the following:

If Cell A3 is 1 then CommandButton5 is enabled and CommandButton6 is disabled

Or

If Cell A3 is 2 then CommandButton6 is enabled and Commandbutton5 is Disabled

Any help would be really appriciated?

Thanks
Marc

Hi all,

Will appreciate if anyone could help me with this. Thanks in advance

Let's say I have with me some data with Column A containing certain numbers for example, from 1 to 10. Now, I'm given a task to colour the row with a specific colour based on the number in Column A. For example, if cells A3, A5 and A9 contain 3, then rows 3, 5 and 9 will b coloured red. If cells A1 and A6 contains 5, then rows 1 and 6 will be green etc. Is there a way to do this using VBA and some shortcut keys? If there's a way, then my work life here will become much more simpler, else we will have to colour the rows manually. So, I would really appreciate any replies that can be of help here. :D

Thank you so much for any replies.

I have to apologize for my ignorance in advance. I know nothing of VB or
embedding macros in Excel. I can learn quickly though. Before I get to the
real question, does anyone have a suggestion for a website that can hold my
hand through the basics of using VB macros in Excel starting from the "I'm
an idiot" stage?

I want a macro that I can electively run on a single sheet that will hide
all rows based on a formula I define. I'd like it to operate based on a
button on the sheet or even just by entering the sheet. Either is fine. I
know this is actually two or three questions in one:
1) how to initiate the macro by entering the sheet or pressing a button
2) how to hide specific rows in a macro
3) how to use a formula in a macro (maybe not really an issue)

Thanks all.
Scott

Hello everyone ... I'm a real newbie. So please take nothing for granted.
Working in Excell 2007 (Wanting 97-2003 Compatible mode).

What I want to do is based on a cell (containing a formula) within each
group of lines and that is to hide that specific group of lines if the
formula equates to TRUE. Also, I would like to un-hide all rows afterwards.
These functions could be linked to two button controls (on the same
worksheet).

Worksheet name = Exhibit1
There is 142 groups of 4 lines (consisting of 20 columns).
The top left cell of each group (A?) is a cell with a formula resulting in
TRUE (=0) or FALSE (0) [based on one cell located in the 3rd line eg:
"=IF(R28=0,TRUE,FALSE)"]

Not sure where to start or where to put the code.

Please help ... any suggestions or solutions would be very much appreciated.
Thanks,

I'm new to Excel and VBA. I try to hide rows based on whether the user enters
a specific word(s)/phrase into a specific cell. For example, if cell M49 has
a value of "i love lucy", then unhide rows 50 through 55, if cell M49 is
empty or has any other value than "i love lucy", then hide the rows. How do I
implement that?

et al,

If this is the wrong group, please point me in the right direction.

What I want to do is choose rows in a spreadsheet based on a criteria
(yes, I know I could filter to get it), but I want to be able to have
them chosen from w/in VBA as I plan to take data in said row and build
and send an e-mail with it.

I'm only looking for the first part, right now. How to choose a row
based on criteria.

Once I have that, I'll probably just write it out to a text file to
make sure it works.

Then, if it shows that it works, I'll go ahead and take out the 'text
file' and replace it w/ building and sending e-mail.

Any help is greatly appreciated.

thx.
-Michael


No luck finding an answer? You could always try Google.