Highlighting Entire Row
This problem has been driving me insane, as I thought it wasn't possible to do in excel however this morning I accidentally pressed a shortcut key on my keyboard and what I wanted, happened.. pity I can't remember how I did it.
How do I highlight an entire row, but remains highlighted when i enter info in the cells until i move down to the next cell? Eg. I click on row"1" so that all cells are highlighted, then while entering info in B:1 - the entire row is still highlighted? This will be very useful in my line of work.
I know there was some information on conditioning formatting, but surely there is a way to do it via keyboard shortcut since i accidentally did it just before.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Highlighting Active Cell's Row, Along With Any Row That Shares Same Value In That Column
Is it possible to click on a cell in column C, and have the wishlist below happen: That active cell's row is hightlighted. Any cell in that column that has the same value as active cell is also highlighted. Plus, any cell in another sheet that has that value it's row is highlighted too. Example: I click on C5 in Sheet 2 its value is 45000789 it row is highlighted, this value also appears in C3 in the same sheet, so it's row is highlighted as well. Plus, in sheet 1 in C10 this value appears and it's row is highlighted as well. When any of the values are clicked again the highlight is removed from all parties.
View Replies!
View Related
Highlighting The Row With The Selected Cell
I work with many sheets that have many data columns and have used a "greenbar" formula in conditional formating but would like to have a similar formula that highlights the current row. If possible, it would be nice if this new formula would not wipe out all other formatting as it moves. I have seen Ivan's work at www.mrexcel.com/hof001.php but it says that it wipes out other formatting. The formula that I am currently using is =MOD(ROW(),3)=2 Is there a way of getting the current row number so that it could be =ROW()=(current row number) ?
View Replies!
View Related
Highlighting A Row And Having That Data Tranfered To Another Worksheet
I have 1 Excel Doc that has 3 worksheets (DATA, COVER, TPA). DATA is where i input the data and COVER and TPA are where the data needs to end up. both COVER and TPA are standardized sheets that i do not change...only the data coming in will change. How can i make it so that whatever row in DATA i highlight, that row's data will be entered in to the correct spots on both COVER and TPA. when i highlight a different row, then the data will change to that row's data in both COVER and TPA (hope i'm explaining this correctly). right now i can only do 1 row by using (=DATA!$D$2). basically i want the '2' that is representing the row to be whatever row i have highlighted. if i highlight row 51 i want the formula to read (=DATA!$D$51). can anyone please help. i can put up a copy of the spreadsheet if need be, i am just hesitant cause of the confidential info. i would need to change the names and numbers to protect the innocent.
View Replies!
View Related
Select An Item Of Data ( Or Update A Particular Cell ) Then I Loose The Highlighting For That Row
I have a big spreadsheet that has 30 columns. The data is downloaded from an SQL database from time to time. The row identifying data ( the record number/key ) is in the left most cell. I often have to move over 10 to 20 columns to view some other data in the same row. I can do this by selecting the preset Excel row number which higlights the whole row and then use the lower slider bar to go to the correct column to view the data. However if I wish to select an item of data ( or update a particular cell ) then I loose the highlighting for that row and I can "loose" which row I am on. ( maybe I'm stupid but it does happen ) Can I use Conditional formatting to highlight a row whenever I alight on any data in that row and it sttays highlighted until I select another value on another row?
View Replies!
View Related
Entire Row Empty
Looking for a formula to establish whether a whole row is empty or not I try this: =LOOKUP(2;1/(4:4"");4:4)=NA() (XL 2007) But, this works, except when one cell contains NA() how to make a general expression?
View Replies!
View Related
Offset Entire Row
I have used the Selection x1Down code to select all rows to the next one with data in but now need some code to deselect the final row (I want to delete all of the blank rows above)
View Replies!
View Related
Copy Entire Row
Here is the code that I have so far and it works almost the way I want it to. I need to copy over more than just that cell that is being coppied over I need to copy over certain columns in that row. Private Sub ListBox1_Click() Dim wksData As Worksheet, wksPayment As Worksheet 'payment sheet Set wksPayment = Sheet11 Set wksData = Sheet26 Dim LR As Long LR = wksData.Cells(Rows.count, "A").End(xlUp).Row Dim varField As Variant varField = wksPayment.OLEObjects("Listbox1").Object.Value Range("F6").Value = varField
View Replies!
View Related
Delete Entire Row
when i delete the entirerow, it shifts the rows up (which is what I want) but it skips that row when it does. so if I have 2 blank cells together it will delete the 1st and leave the 2nd blank. Sub () Dim myrange As Range Set myrange = ("a:a") For Each c In myrange If c.Value = "" Or c.Value <= 5 Then c.EntireRow.Delete End If Next End Sub
View Replies!
View Related
Copy Entire Row From One Ws To Another
I have looked through the forum at several different copy and Vlookup examples, but I can’t seem to get them to work. I need to compare worksheet(1) (“Portfollio”) in column “B” against Worksheet(2) (“PJS Cap”) column “B” to find any items that the worksheet(2) has that Worksheet(1) does not. Then for these items copy the entire row to the end of worksheet(1). My code gives a runtime error on the copy line Sub PJSUpdate() Dim Found As Boolean Dim I As Integer Dim J As Integer Dim LastRowE As Long Dim LastRowP As Long Dim InsterRow As Long LastRowE = Worksheets("PJS Cap"). Range("b65536").End(xlUp).Row LastRowP = Worksheets("Portfollio").Range("b65536").End(xlUp).Row InsertRow = LastRowP For J = 2 To LastRowE ' loops through the portfollio worksheet project list Found = False 'flag intitated to not found........................
View Replies!
View Related
Copy Entire Row To New Worksheet
I've a worksheet where I maintain the IT tickets that I handle. Whenever, the I column is updated with Implemented/Closed, the entire row should be copied to a new worksheet. I've tried with other VBA scripts provided, but was not success with the same.
View Replies!
View Related
Delete Entire Row Of An Array
I want to know if there is a way to delete an entire row from an array? I have a 2 dimensional array and I want to loop through the elements in the first column and delete an entire row based on some conditions. Since the number of rows are too many usual looping takes a long time so I want to use an array.
View Replies!
View Related
Pasting Entire Row Into A Different Workbook
I'm running a code to copy rows from one workbook and paste into another...Code runs but doesn't paste anything into the workbook. Sub Macro2() Workbooks.Open Filename:="J:BestExIncMikey_copy.xls", UpdateLinks:=3, ReadOnly:=False Dim c As Range Dim rng As Range Dim ws As Worksheet Dim Row As Integer Dim LstRow As Integer Dim i As Variant For Each ws In Worksheets Select Case ws.Name Case "Total Locked Pipeline" Range("I2").Select Selection.End(xlDown).Select...........
View Replies!
View Related
Delete The Entire Row That Has The Text Pc.
I use the code below to delete the entire row that has the Text "Pc." in it. How can I make this delete the row with "Pc." plus the next row? Sub PL() Dim i As Long For i = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1 If InStr(Cells(i, "A").Text, "Pc.") Then Cells(i, "A").EntireRow.Delete End If Next i End Sub
View Replies!
View Related
Deleting The Entire Row In VBA
I have a spreadsheet with thounsands of rows. Some of the rows I'd like to delete because thier values are below 5%. I wrote a VBA code, which primary function is to look for a cell and delete the row if the value is less than or equal to 5%. The code looks fine but I get an "Infinite Loop" anytime the first row value is not true. Below is the code, -jungleman1 Sub Delete_Zero_Factors() Dim i As Integer Dim CUSIP As String Sheets("Report").Select Range("B5").Select i = 0 While Not (IsEmpty(ActiveCell.Offset(i, 0).Value)) CUSIP = ActiveCell.Offset(i, 0).Value Do While (ActiveCell.Offset(i, 1).Value
View Replies!
View Related
Check Whether Entire Row Has Been Selected
I have a button on an excel sheet call 'Delete Row' where I want to put code underneath so that a user can delete an enirerow. I have the Selection.EntireRow.Delete Shift:=xlUp at the moment but I want to check that the user has selected the entirerow before I delete it.
View Replies!
View Related
Hide Entire Row Before Printing
I have used this code in the past to hide an entire row if between the Range of F3 to 500 the value is zero. What I’m trying to do is change the range from instead of Rows 3 to 500 TO from Row 3 to the last row of data in Column “C”. What I have is a worksheet which may have as many as 5000 lines of data in column “C” however Column “F” may only have data in 5 of those lines. Instead of printing a 100 pages which may have no relevant information, I only want to print the rows which have values in column “F”
View Replies!
View Related
Vba Autofill Entire Row
I have recorded a Macro to AutoFill a row in my Excel spreadsheet. The VBA code is very simple and looks like this: Selection.AutoFill Destination:= Range("D8460:T8460"), Type:=xlFillDefault Range("D8460:T8460").Select I thought I would simply be able to click on another row and re-use the Macro, but this doesn't seem to work, as I get the following error message: AutoFill method of Range class failed. What do I need to change in my code, so that it will work in any row I choose?
View Replies!
View Related
Read Entire Row Into An Array
I have a workbook with about 700 rows and about 40 records on each row. about 2-7 a day of these. The procedure I currently use a loop for does 2 different things. 1 I collect only a few of the records from the line and place them in a separate sheet .I collect the individual data, identify the ID ( cell F) and then search for the corresponding ID on a nother sheet when I do find it I add the info to the sheet on the last + next row. (I sort later) 2 I collect the entire row and place it on yet a nother sheet. The whole row has information that is categorised by the content of Cell A (name) so I copy the row and paste it into a separate sheet. What I would idealy like to do is to copy the entire source sheet into an array once, and then copy the data into each target sheet using a loop to get each record from the array. Using only 1 loop to first read the separate data from the " array row" and place it into the first target sheet (procedure 1) and then switch the workbook and copy the whole "array row" into the target sheet there (procedure 2). At the moment I read info on 1 row switch to the target sheet and place the information there, then I "copy" the row and switch to the next workbook and paste it there then I go back to the source and do the next row. Obviously Im no programer and that was the best way of doing what I didnt know how to do better . Is there a way that a whole row can be stored in an array and then 'copied' onto another row on a nother sheet without reading in the individual cells into separate elements. That would be the worse case scenario but a much faster way of doing what I curently have.
View Replies!
View Related
Select Entire Row With A Named Variable
I have this problem quite often and always have to think of a way around it, however I think there must be a simple method of doing what I want. If I want to select row three, I would write: Rows("3:3").Select However, if I have a variable which holds my row number how do I rewrite the above code? I've tried serveral varations of: Rows("CurrentRow:CurrentRow").Select
View Replies!
View Related
Highlight Entire Row When Word Is In Cell
What I want to do is select all rows that contain the word conveyor in it. So far I have managed to select all the cells, I have also managed to select the entire row but one row at a time, I have even been able to turn all the cells a different color but I just want to highlight them for other formatting, copying, and several other things that need to be done. I have other documents this could be very useful in too so i want to make it a generic multi-line highlight rather than adding the formatting into the formula.
View Replies!
View Related
Highlight Entire Row Based On Cell Value
From what I understand this is a simple process but since I am new to programming and I am in need of help. I am trying to write into one of my macros that as it is searching in the sheet if it finds CRL in column C and if it finds a value between -1 and 100,000 in column K, then it will highlight the entire row green.What can I do to add this into my macro?
View Replies!
View Related
Copy Range ISO Entire Row
I have below macro button code, when i click on any row and enter button, entire row gets copied and paste in other sheet. Can we ammend this formula so that it should not copy entire row, instead it should copy range from row A to row Q only as i need to utilise other coloums which should not get copied to other sheet. Private Sub CommandButton1_Click() ActiveCell.EntireRow.Copy Sheets("FWD Mary Ann").Range("A65536").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues ActiveCell.EntireRow.Activate End Sub
View Replies!
View Related
Delete An Entire Row If Condition Is Not Met
I'm working on Outlook 2003 and Word 2000 which are not compatible. I have an Excel Sheet which are my contacts from Outlook and I want to re-work it to have only a certain category left. I need a macro that does - search a certain column - deletes the row if it does NOT find a certain condition Ideal would be a little pop up window to enter which category I want left.
View Replies!
View Related
Highlight An Entire Row And Then Run A Macro
Here's what i am trying to do... I have a workbook with lots of info, i believe there is a way to highlight an entire row and then run a macro that will then collect the info from certain columns (along the specific highlighted row) say, columns A-F and M & P for one example, the collected data will then be copied to a new pre-designed template, and pasted into set cells. The cells that the data will be pasted into will not be along one row though, the pre-defined template will resemble say, an application form Can this be done, I understand macros only at a basic level currently but am trying to understand the VBA way of creating them too now.
View Replies!
View Related
If Column B Is Blank Delete Entire Row
I am using the following macro to delete "completely empty" rows. I also need to delete some rows if a cell in column B has no value. How would I change this macro? ' DeleteBlankRows Dim r As Long Dim C As Range Dim Rng As Range On Error GoTo EndMacro Application.ScreenUpdating = False Application.Calculation = xlCalculationManual If Selection.Rows.Count > 1 Then Set Rng = Selection Else Set Rng = ActiveSheet.UsedRange.Rows End If For r = Rng.Rows.Count To 1 Step -1 If Application.WorksheetFunction.CountA(Rng.Rows(r).EntireRow) = 0 Then ActiveSheet.Rows(r).EntireRow.Delete End If Next r EndMacro:
View Replies!
View Related
Copy And Paste Entire Row In Worksheet
I want to run a macro that looks thru column F of all my worksheets (in my entire workbook). If it finds an x then I want it to copy that entire row and paste it into a worksheet called "old" or a new worksheet or a new document in word (it doesn't matter - just whatever is easiest for my little brain to understand). I should end up with about 40 rows of data in the new sheet. (I have some code that looks thru it all and colors the cell blue but I just don't know how to get it to copy and paste the entire row into a new worksheet.)
View Replies!
View Related
Conditional Format For Entire Row Upon Value Change
I can't figure out to place conditional formatting on a range at each change in cells in column A....ie. in column A, several rows will have the value "1", then several will have the value "2", all the way through "52"...sometimes there could be 4 rows with the same value, otheres there may be 5, or others even only 1.....so I need conditional formatting that says each time the value in column A changes, highlight all cells in that row one color and change that color at each change. Does that make sense?
View Replies!
View Related
Delete Entire Row Based On Selection
I have a worksheet where I have restricted the users from scrolling around beyond a certain range. Because of that the user is prevented from selecting an entire row. There are some cases where the user should be able to delete entire row or range of rows. How can I allow them to select say cells "C17:C35" [they would just hoghlight the range] and hit ctrl+d; which would trigger a macro and the macro in turn would delete all the rows in that selection [rows 17 through 35]? I know some basic codes for deleting entire rows, but I don't know how to read first and last row of the selection under Target property.
View Replies!
View Related
Delete Entire Row Based On A Condition
I have a problem deleting rows based on a condition, i didn't know how to use offset method. i'm using a table like this one : A B C 1 Main design1 FFR0 2 Extra design2 FFR0 3 Main design3 FFR0 4 Extra design4 FFR0 . . n Main designn FFR0 I want to delete entire row if "FFR0" in column C exists in a row beginning by "EXTRA" (column A)
View Replies!
View Related
Copy The Entire Row Depends On A Cell Value
I would like to create a macro for my boss, their requirement is, in sheet 1 , they have one column, To chase . So when type "y" in any of the cell in a 'To chase' column, then copy the entire row and paste to sheet2. Also is it possible to do this any selection change event of the cell?
View Replies!
View Related
Delete Entire Row If Cell Equals
I have a row range 5:20004. In that row range column R may have 'QLD' in the cell. I am trying to delete all rows within that range that have QLD in column R or delete the row if R <> 'NSW' (would be a better way just in case there is something other than QLD) to do this on opening the file automatically.
View Replies!
View Related
|
TRACKER
