What I want to do is go to the worksheet that has the largest number of rows (it's always the same worksheet, so I know which one it is), set the zoom factor for THAT worksheet (which I can do, and it always has the same number of rows), and then take THAT zoom factor, whatever it is - and it will vary depending on the user - and apply that to the other worksheets that have a similar layout. However, the file contains 8 sheets that are all identically laid out, except the number of rows is different.
HOW TO ADD AN UNDERLINE IN EXCEL CODE
I have set up an auto-execute macro which automatically sets the zoom factor to best fit, for several of the worksheets, and this works fine.īy repeating this code for each worksheet, I can make each one be zoomed just right. I have set it up so that everything looks OK and is visible on MY screen, but I'm conscious that some users may have different screen sizes, different toolbars set up, and so on, which might make some parts not immediately visible to them. I have set up a workbook that is sent out to lots of different users. Some one please revert with the solution. 1st time when I am running the macro the first row in the visible filtered data is starting at Cell address A4 and next time when I will run the macro it may be A6ģ) The Code is also selecting the 1st row which is a header row. I need this to select the the data only till the last used row in the given range.Ģ) It is not possible to provide the address of the first row after we apply the filter since the first row address may change depending on the values in the table.Į.g. Range("A:p").SpecialCells(xlCellTypeVisible).Selectġ) after applying the filter, while selecting the data it is selecting all the rows in given range till last row on the workbook. I am using below code to Select the Visible rows in the target range:
HOW TO ADD AN UNDERLINE IN EXCEL HOW TO
Actually I know how to select the data after applying the data filter but the issue is I am not able to exclude the header row and give the target range as used (non-blank) rows only!!
I am looking for the code to select the visible data after applying a data filter.
Sheets("Sheet1").Unprotect Password:="temp"ĪctiveSheet.EnableSelection = xlUnlockedCells ' If Not Intersect(Target, MRange) Is Nothing Then Sub Worksheet_Change(ByVal Target As Excel.Range) I think I am close, but I am getting an "End If without block If" error on the If Clause. If the user selects that cell again, they will get the usual pop-up message, "The cell or chart that you are trying to change is protected." If a user enters a date in a cell or range of cells anywhere in the column, the changed cells also need to be locked and protected (Once they enter a date, it is not allowed EVER to be changed again.
I have named the cells in that column "MS96A". I have a worksheet that is locked and protected now, except for cells in a certain collumn. Ideally this should be the case isnt it ?Īny suggestions on how to go about this. if i really want to join the sentense all i have to do is "=A1&B1&C1&D1&E1&F1" (without quots)Ĭan someone come up with a function to underline just 1 cell Now my question here is for some one who can put up a function for a sell only or formatting. Therse were the best two answers i could find out for underlineing a part of a sentence in a cell. Length:=1).Font.Underline = xlUnderlineStyleSingle If m圜ell.Characters(Start:=l_Position, Length:=1).Font.Underline _ĬopyNumberWithOneUnderlinedCharacter.Characters(St art:=l_Position, Range("A1").Characters(InStr(1, Range("A1"), "Customer", vbTextCompare), Len("Customer")).Font.Underline = Trueįunction CopyNumberWithOneUnderlinedCharacter(ByVal m圜ell As Range) Range("A1").Characters(InStr(1, Range("A1"), "Company", vbTextCompare), Len("Company")).Font.Underline = True ' add underline to the format of the words "Company" and "Customer" S1 = " The Agreement between (The ""Company"") and " Private Sub Worksheet_Change(ByVal Target As Range) you can do it but only if it is broken up.Īnd have been trying really hard to find en number of ways. I've been doing alot of research for the past week on this topic and came up with top two answers.
0 kommentar(er)
