Find End Of Column And Bold Rows In Loop
Oct 17, 2007
I am trying to Find a cell containing a string ("derf" in this case). Then bold the entire row and continue to loop till it hits the end of the row and column.
Here is what I have.
Sub Macro3()
Range("B65536").End(xlUp).Select 'Finds the bottom in column B and sets it to endhere
ActiveCell.Value = "endhere"
Range("A1").Select 'goto the top
Do Until ActiveCell.Value = "endhere" 'The Do Loop Starts
Rows("1:30").Select
Set rfoundcell = Selection.Find(What:="derf", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
With rfoundcell
rfoundcell.EntireRow.Font.Bold = True
End With
Loop 'Keep looping till it hits the bottom of the columns row!
End Sub
View 2 Replies
ADVERTISEMENT
Jun 8, 2008
I am merging columns. Col B has all text in bold. Col C has all text in regular font. When I use ASAP to merge, the merged column comes out all bold. When I use a formula to merge the merged column comes out all regular. What I want is for the merged column to contain the bold text from Col B in bold, and the regular text from Col C in regular.
What I want is...
This is for service plan B: Take all data and transer to new server.
What I am getting is...
This is for service plan B:Take all data and transer to new server.
OR
This is for service plan B:Take all data and transer to new server.
View 9 Replies
View Related
Aug 21, 2009
I have an Excel workbook that i need to loop through all rows searching for a certain string we can call "Totals". There are more than one, and i need to grab the values from each at an offset in the same row, different column, to total at the bottom. I have tried a few things unsuccessfully. The code below is just my first step to try to find the row, i know there are problems as i am unfamiliar with the different properties of a given variable.
Function FindRow(szName) As Integer
nFoundRow = 0
For nRow = 0 To Worksheets("Totals").Columns(nCol).Rows.Count
If StrComp(Worksheets("Totals").Cells(1, nRow), szName) = 0 Then
nFoundRow = nRow
End If
Next nRow
FindRow = nFoundRow
End Function
Sub Totals()
nCol = FindRow("Totals")
ActiveCell.Select = nCol
MsgBox (nCol.Value)
End Sub
View 9 Replies
View Related
Apr 24, 2009
In the code below I find rows containing "$$ #" at the far left and process it.
For all the remaining rows I want to delete the entire row, my macro only deletes some rows. Obviously I am not grasping something about looping throught the rows to delete unwanted data.
View 2 Replies
View Related
May 27, 2014
There are groups of similar ID numbers in Column J. For a group of similar ID numbers in consecutive rows there is only one row that has a number greater than 0 in its Column L cell and the rest of the cells of Column L for that set of similar IDs is filled with 0s.
First for that unique ID group I need to find out which row is it that has a value greater than zero in its Column L cell.
Then I need to use that value to fill the rest of the 0s in Column L corresponding to that set of Unique IDs.
The process continues with identifying similar IDs in Column J and this time doing the same thing for their Column M. I have attached a sample file that shows the data and how the results need to look like.
View 3 Replies
View Related
Sep 12, 2012
make and loop that loops true all values in column A and B. For example, if the cell A3 is higher the cell B3 then I want it to write the higher value in new sheet.
View 2 Replies
View Related
Aug 17, 2007
I need to create a loop within a loop. First loop works fine as follows:
-------------------------------
x = 0
Dim DirArray(0 To 80) As String
Dim intI As Integer
For intI = 0 To 80
DirArray(intI) = Range("start").Offset(intI, 0).Value
Next
For x = 0 To 80
lob = DirArray(x)
Range("lob") = lob
Calculate
Sheets("WBB LOB Summary").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next
----------------------------
At the 'start' point, first loop works downward through rows. Second loop would work across columns in each row within the first loop. I will have to use a variable for 'y' (the across value) b/c the number of cells containing data can vary between 3 & 9, but I have a COUNTA in each row setup to use as the variable.
So spreadsheet data would look like this with A being the 'start' cell:
A 1 2 3
B 1 2 3 4 5 6
C 1 2 3 4
I need to pickoff A1, A2, A3, B1, etc, picking up every sub-category on the sheet.
View 6 Replies
View Related
Jun 21, 2007
Instead of bolding only the cells with an asterick in them it will bold every cell with text in it. I also would like to adapt this code to only search through column A but have not been able to figure it out.
Sub FindIt()
Dim c As Range, firstaddress As String, ws As Worksheet, FindMe As String
FindMe = "*"
For Each ws In ThisWorkbook.Worksheets
With ws.UsedRange
Set c = .Find(FindMe, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
c.Font.bold = True
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
End With
Next ws
End Sub
View 5 Replies
View Related
Oct 15, 2008
I am using ctrl+f to find a string that may or may not be in a worksheet.
If it is found, i need to bold the row.
If it is not found, i need to go on to the next instruction in the macro.
I just don't know quite how to combine the If/Then with Find.
View 9 Replies
View Related
Nov 20, 2012
What I have is 3 columns, all containing numbers
What I want to do is delete a row if the middle column is less than 1.
However my loop seems to skip a row if the column data is like the below
a
b
c
1
1
1
1
0
1
1
0
1
1
1
1
1
0
1
End
Code:
Range("A2").Select
Do
If ActiveCell.Offset(0, 1) < 1 Then
Range(ActiveCell, ActiveCell.Offset(0, 2)).Select
Selection.Delete Shift:=xlUp
End If
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Value = "End"
View 5 Replies
View Related
Jul 26, 2009
I have one column of names in excel. The column may contain more than one row with the same name but these rows with the same name will all be grouped together. This is an exampe (each name represents a row in column A):
ColumnAColumnB
andrewData
julieData
julieData
julieData
jonathanData
jonathanData
What I want to do is copy the rows with the same information, e,g, the rows with 'julie' above, paste them into a new spreadsheet and email this spreadsheet to specific email addresses and then do the same for 'jonathan'.
I can work out how to send an email using VBA but I am really stuck as to how to go through the rows and send the email in discrete 'chunks'. I have tried using a for next loop, looping through the rows and copying/pasting rows that are the same as the previous one into a new spreadsheet but this does it one row at a time.
If I include the instruction to email the spreadsheet within the loop this would also email the new spreadsheet one row of information at a time, i.e. three emails for 'julie' each containing a spreadsheet with one row of information on it, rather than one email containing one spreadsheet with all three rows on it.
View 4 Replies
View Related
Oct 18, 2008
when the largest number in column B the hotel in column A should be in bold.
So in excel language IF(Number in B Is Max display corresponding hotel in column A as BOLD. But I can't figure out how to do this.
You can see here on the image:
additionalimage.gif
View 4 Replies
View Related
May 20, 2008
I have a worksheet on which the data is already grouped. At the top of each group is a row that contains only the group name. Since the rest of that row is blank, I want to use a blank cell on that row as a reference, then copy the group name to a newly created column, then fill that column down to the next group.
The goal is to create a column that contains the group name, rather than just having the group name as a " header" at the top of each group.
View 5 Replies
View Related
Jun 19, 2009
I need to create a macro that will examine a range of cells, for example D12:T12, find the cell with the largest number value in that range then set the font for that cell to bold (or red, or change the cell background color).
View 2 Replies
View Related
Oct 7, 2013
I have an Excel sheet with 2 columns : Date and TextColumn
The TextColumn column contains in each cell text such as this :
Title1
TextLine1
TextLine2
Title2
TextLine3
TextLine4
....
I need to split each line as follows : Each title (in bold and on a new line) and the text who follows should be placed in a new row, copying the date in each new row created.
View 1 Replies
View Related
Aug 3, 2006
I need to combine rows that have the same value in column a and column b to the same row by offsetting column c to the next available column. For example, I would like the first 6 rows of the provided sample to appear like this.
0014B22<@44>Soil Properties and Qualities<@44>Soil Properties and Qualities<@44>Soil Properties and Qualities
0014B23<@28>Coursey<@28>Ogles<@28>Shelocta
Sometimes the values are the same in column c, sometimes they are different. I do not want to delete duplicate rows where they are the same. Sometimes there are 2 rows that have the same values in column a and column b, other times there may be 3 or even 4 rows with the same values in column a and column b. Regardless, I would like the values in column c combined on the same row in the next available column. It would be nice if the duplicate column a and column b rows (with a null column c cell) were then removed, but I could do that in the next step.
0014B22<@44>Soil Properties and Qualities
0014B22<@44>Soil Properties and Qualities
0014B22<@44>Soil Properties and Qualities
0014B23<@28>Coursey
0014B23<@28>Ogles
0014B23<@28>Shelocta
0014B24<@33><i>Available water capacity:<p> High (about 11.5 inches)
0014B24<@33><i>Available water capacity:<p> Very low (about 2.9 inches)
0014B24<@33><i>Available water capacity:<p> High (about 9.0 inches)
0014B25<@33><i>Slowest saturated hydraulic conductivity:<p> Moderately high (about 0.57 in/hr)
0014B25<@33><i>Slowest saturated hydraulic conductivity:<p> High (about 1.98 in/hr)
0014B25<@33><i>Slowest saturated hydraulic conductivity:<p> Moderately high (about 0.57 in/hr)
0014B26<@33><i>Depth class:<p> Very deep (more than 60 inches)
0014B26<@33><i>Depth class:<p> Very deep (more than 60 inches)
0014B26<@33><i>Depth class:<p> Very deep (more than 60 inches)
0014B27<@33><i>Depth to root-restrictive feature:<p> More than 60 inches
0014B27<@33><i>Depth to root-restrictive feature:<p> More than 60 inches..............
View 9 Replies
View Related
Mar 11, 2013
The following code doesn't seem to work for me except when I use an actual number.
Set MR = Range("A1:A100")
For Each cell In MR
If cell.Value = "*3L*" Then cell.EntireRow.Font.Bold = True
Next
I am attempting to bold the entire row if the macro finds text that begins with 3L in a tab. If I change the code to look for a number, then it works fine.
Also - Is there a way to make this run on specified tabs? For instance, if I have four tabs:
January
February
March
Q1
Say I only want it to run on the January, March, and Q1 tab, how would I go about doing that?
View 2 Replies
View Related
Mar 2, 2014
Code that will turn the entire rows text bold and red if a cell in that row has the word.
New PO
View 9 Replies
View Related
Sep 7, 2007
How to count cells in a column that are in BOLD
I looked all over couldnt find it.
View 9 Replies
View Related
Jan 30, 2007
Every week I have a worksheet compiled with large range of data, being input after many meetings done. The action-owner Names in open/close brackets are among the long text input in one Column (there are not more than 50 rows of data).
My problem is that in the Column every time I have to manually Bold-face the owner names in the cells that have their names, pressing F2 and Bold. Examples: ...(Wilfred)... or ...(Jacky/Joseph)... Can someone please help me with a code, say I can list out a fixed list of selected text and such code will be able to have these selected text (which is names in this case) in the cells bold-faced.
View 7 Replies
View Related
Sep 11, 2013
I have working code that returns a row number within a for loop based on parameters I set.
Each time the for loop runs I would like to store this row number, then after the loop has finished, delete all stored rows.
Code:
for rowNum = 1 to x (some variable end row number which I already have worked out using End(xlUp).Row)
if x = y then
*storedRow = rowNum
end if
next rowNum
*
Lines with a * are the bits I can't work out. I've been trying to understand arrays by reading posts on what other people have done, but I can't fit (or fully understand) the reDims, or reDim preserves into my code. I've seen what appear to be quite complex ways involving uBounds and LBounds, but unfortunately I can't see how to use them.
All I want is to simply keep adding a row numbers to a variable, (i.e. row 2, 5, 20, 33, 120, etc) and then delete those specific rows.
View 4 Replies
View Related
Feb 28, 2014
I have a file with many different row sizes and after calculating the figure, we will need to add up the total for a particular job. Due to the irregular data on each job (some with only 1 line item where sometimes the job have few line items).
The cell to sum already has a formula for the data.
For example, I already have a formula on Column L to get the figures. After few line items, I will need to add a total to the lines item, also on Column L. I think we can uses 2 sets of data from 2 different column as "pointers", Column D and the formula column itself (but I don't know how to). The columns that I need a sum function will be from Column L to X. After doing the sum, can we at the same time draw a sum line? (single top line and double bottom line).
Am I being "too greedy" ? Imagine if we have more than thousands lines of such data to be added manually and to draw these lines manually.
View 3 Replies
View Related
Aug 5, 2012
How do we run a macro if the selection is not bold and If bold then do nothing.
View 3 Replies
View Related
Oct 2, 2007
I am trying to create a macro that if a value in a cell in column A is bold, then the value in the same row in column H is bold, then loop it to run on the rest of the worksheet.
View 9 Replies
View Related
Nov 12, 2009
How can I change the format ie. Bold Text in one coloumn if the the data in another column is checked.
Basically, want the text in corresponding row of column "B2:B124" to be bold if the check box in the perspective row is checked?
View 9 Replies
View Related
Feb 23, 2009
I have searched high and low for a more efficient macro than the current loop i have. I have a range that varies in size but always follows the same format. After i reach a certain value output from a formula, "4", I want to delete all lines below that line. So I want to find value "4" then move one row down and delete all lines to line 2004. I have attached the macro that i compiled but it takes a couple of minutes to run.
Sub manual()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
. ScreenUpdating = False
End With
With ActiveSheet
.Select
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView.................
View 2 Replies
View Related
May 4, 2014
I'm experimenting with a For Loop, and I don't know how to "tell it when to stop at the end of the column", because the columns have an empty row and then more data.. then an empty row and more data etc So the end of the column would be where there are two empty rows.
Code:
Sub eighteenrowsdown()
'
' eighteenrowsdown Macro
'
'
Dim rng As Range
Set rng = Sheets("Sheet3").Range("I3", "AE3")
Dim i As Integer
[Code]...
I know the code is bad but as long as I can get it to stop at the end of the column I'll be happy
View 6 Replies
View Related
Jun 10, 2008
I am trying to create a macro that will delete everything below a certain word.
The code I tried to use is the following. This produced an error:
If Range(Cells(1, 1), Cells(180, 1)). Find(" Interest Expense").Row = 0 Then
iStart = Range(Cells(1, 1), Cells(180, 1)).Find(" NET INCOME").Row + 1
Else
iStart = Range(Cells(1, 1), Cells(180, 1)).Find(" Interest Expense").Row + 1
End If
Rows(iStart & ":" & FinalRow).Delete shift:=xlUp
View 4 Replies
View Related
Jun 3, 2009
Hi, I'm very new to writing Excel Macro's and wanted to know if I could do the following. Conceptually, I understand what I need done and think it should be fairly straightforward.
There's 2 main events in this loop (I hope that's the correct terminology):
Input 1) User defines the beginning cell to start the loop. In this case, A2.
Input 2) User defines the range of columns/rows to display. The formula for rows that I've thought of is 4r. So if a user wants 20 rows below cells A2, they simply input 5 for r. The number of columns is a constant 5. So if r=5, then I'd want the range to be A2:E22......
View 13 Replies
View Related
Feb 20, 2007
I'm trying to use this Find Method and combine it with a countif or loop. Something that will count a number of occurences of a unique type of character. I'm looking to find all "F" characters in Bold, Italic and Size 16. Here's my find code that I'm trying to use. I can get it to work by itself but not along with a countif or loop.
Sub count_4()
Dim r As Range
Set r = Range("A1:A6")
With Application.FindFormat.Font
.Bold = True
.Italic = True
.Size = 16
End With
r.Find(What:="F", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, searchformat:=True, MatchCase:=True).Activate
End Sub
View 4 Replies
View Related
