Help me - I'm still learning

[newbie10]

Hello all, I'm attempting to write a simple piece of code, and I can't seem to get it to work just right. All I want it to do is look between a range ("B8:B" & lastRow) and if the characters are over 93 (or can't all be seen, 93 is just the count for the row size of 54), that the row will auto height and only that row, otherwise it leaves it at a standard height of 54. I know my code is wrong, but here is what I have

Sub Row_height()
Dim LastRow As Long
Dim cell As Range
CurrentSheet = ActiveSheet.Name

With Worksheets(CurrentSheet)
LastRow = .Cells.Rows.Count.End(xlUp).Row
For Each cell In Range("B8:B" & LastRow)
If cell.Characters.Count >= 94 Then
EntireRow.RowHeight = AutoFit
ElseIf cell.Characters.Count < 94 Then
EntireRow.RowHeight = 54 End If
Next
End With
End Sub

:banghead: Any help would be greatly appreciated. Thanks so much.

[Bob Phillips]

Untested

Sub Row_height()
Dim LastRow As Long
Dim cell As Range

With ActiveSheet
LastRow = .Cells.Rows.Count.End(xlUp).Row
For Each cell In Range("B8:B" & LastRow)
If cell.Characters.Count >= 94 Then
cell.EntireRow.RowHeight = AutoFit
Else
cell.EntireRow.RowHeight = 54
End If
Next
End With
End Sub

[GTO]

Hello all, I'm attempting to write a simple piece of code, and I can't seem to get it to work just right. All I want it to do is look between a range ("B8:B" & lastRow) and if the characters are over 93 (or can't all be seen, 93 is just the count for the row size of 54), that the row will auto height and only that row, otherwise it leaves it at a standard height of 54. I know my code is wrong, but here is what I have...

Greetings newbie10,

I see that you just joined is recently. Let me be the first to welcome you to vbax! You will find some mighty nice folks here, and I am quites sure that you will be most glad that you joined. I still marvel at the fact that I "lurked" the site for so long before "taking the plunge" and joining.

Anyways, to your question, whilst I am off to bed in a bit (which should have been a but ago), I am not understanding your question; so maybe this will help another 'answerer'.

You mention if the LEN() of the cell (length as character count) is over 93, we want to increase the row's height. Does this mean that the cell wraps text, or, do you actually want to increase the column's width?

Mark

[newbie10]

Mark,

Thanks for the hardy welcome. I'm determined to learn VBA, and I appreciate all the help so very much.

As far as the length, maybe I didn't phrase it right. This row is a task tracker. The task will be written in Field "B". I have set up a macro to put all the data, with all the formulas in automatically, and then it autosorts. The issue is, after it auto sorts, the row gets "potentially" moved into a different order. If the task is long, then you can't read the entirety of the entry. I basically want the code to look for any "task" that is too long, and autofit the row so it can be easily read. 93 just happens to be the number that fits perfectly in a row height, and column that I set up. I do have wraptext enabled, but that just makes it stay in the cell. I need the row to autoadjust based on the task length. I hope that clears it up. Thanks so much in advance. I'm a military guy, not a programmer. So, I'm trying to teach myself geek! Take care.

[newbie10]

Thanks for the help, I'm still getting a debug error in trying to select the last row. I'm not sure how it's improperly formatted. Thanks again.

[Bob Phillips]

That line should be

LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

[newbie10]

Thanks for the help. I had to make one small adjustment: cell.EntireRow.AutoFit instead of cell.EntireRow.RowHeight = AutoFit but it works like a charm now. Thanks again for all the help. Let me know if you ever want a military coin...to show my gratitude for my first project!

[newbie10]

Final functioning code for anyone that needs it!

Sub Row_height()
Dim LastRow As Long
Dim cell As Range

With ActiveSheet
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
For Each cell In Range("B8:B" & LastRow)
If cell.Characters.Count >= 94 Then
cell.EntireRow.AutoFit
Else
cell.EntireRow.RowHeight = 54
End If
Next
End With
End Sub

[Bob Phillips]

Whilst I am pleased it is working for you, what happens if they change the font, or the font size?

[newbie10]

Xld,

This piece of code is at the end of another much larger piece of code. Essentially, it has a shortcut key, and the user is able to add a new "task". The way the task is entered is via an input box. The inputbox then drops in the information in the correct cells.

My task is to figure out how to password protect the entire task. I want to be able to put a piece of code to set a password, and then lock the task so that it can't me messed with after it's input. I will let you know how that goes. Thanks again for all the help.

[newbie10]

My Macro that I have developed does the following tasks:

When checked the checkbox makes "H" in the active row true.

When H is true, the task that is in that row gets the "strikethrough" font, and the task is essentially complete.

What I can't seem to make it do right:

I'm tying to include in that macro the ability to "unlock" the activesheet with a built in password, then unlock the active cells, "allow" the strikethrough, copy the task into an archive sheet that's hidden, then lock up the row, and re-enable protection on the active sheet. Long story short, it clears up the completed tasks, puts them into an archive sheet, and then removes the row. Here is what I have so far:

Sub unlock_row()
With ActiveSheet
If Range("H8") = True Then 'I know that the 8 is wrong, but I"m not sure how to tell excel to chose the number for the active row
ActiveSheet.Unprotect Password:="test"
'unprotect
Rows("8:8").Select 'again, still not sure how to tell excel to pick current row, instead of just putting the number in
Selection.Locked = False
Selection.FormulaHidden = False
'archive
Range("B8, c8,d8,g8").Select 'it would be great if the program would just recognize the strikethrough, and copy it based on that criteria
Range("g8").Activate
Selection.Copy
Sheets("archive").Select
ActiveSheet.Paste.Range ("a1") '
Else
End If
Rows("9:9").Select
Selection.Locked = True
Selection.forumlahidden = False
ThisWorkbook.ActiveSheet.Protect Password:="test"
ActiveSheet.Select
ActiveSheet.Protect drawgingobject:=True, contents:=True, Scenarios:=True
ActiveSheet.Select
End With

End Sub

I know I'm all screwed up...but any help would be awesome.

[Teeroy]

To answer your questions:

To reference col H in the active row :Range("H" & ActiveCell.row)
To reference the active row :Rows(ActiveCell.Row)

It looks like you've built the code from a macro recoding which, although a good way to start, makes inefficient code. In general you don't need to select and activate ranges to work with them. eg the copy


i = ActiveCell.Row
Range("B" & i & ":D" & i & ", G" & i).Copy Sheets("archive").Range("A1")

[newbie10]

Yes, the code was done with record macro. I am definately begining to see what you mean about ineficient code. You helped out a great deal, now I'm stuck again.

I'm trying to get the script to chose based on conditionalformat criteria. When I put the if statement in, and use conditional format, count is about the only option I recognize. I need the conditionalformat for font, specifically strike through.

Dim cell
For Each cell In Range("b1:b3")
If cell.Conditionalformat.Font.Strikethrough = True Then
cell.ActiveCell.Row.Select
End If

needless to say, this doesn't work. The strikethrough is based on a conditional format that is activated when a checkbox is selected. i.e. "crossing off the task". Just using the cell font strikethough doens't work.

[Teeroy]

A sample of the spreadsheet would help but couldn't you check for the checkbox to move the row rather than the strikethrough (which is the result of the checkbox)? Assuming you only have one conditional format try the following:

Dim cell
For Each cell In Range("b1:b3")
If cell.FormatConditions(1).Font.Strikethrough Then 'you don't need to specify =True for a Boolean test
cell.ActiveCell.Row.Select
End If
Next

[newbie10]

Teeroy,


Well, I had to modify your code slightly. I couldn't get the one you posted to work right. Here is what I have:


Sub Row_select()
'
' Row select
'
Dim cell

For Each cell In Range("H7:H14") ' I will be modifying this to look for the last row a bit later
If cell Then
cell.EntireRow.Select
End If
Next cell
'

End Sub




Unfortunately, all it does is select one row. It won't select multiple rows. Instead of using the "formatcondition", I just used my hidden cell in H, because when you hit the checkbox, that turns true, which kicks in the conditional formatting that causes the blue fill in, and the strikethrough.

The checkbox is for the end users to mark off their tasks. This script will be for a "clear completed tasks" button that will select the completed tasks, archive them, and remove them from the spreadsheet. A micromanaging tool yes, however, it's what the big bosses want. I'm a simple PM.

Thanks again for all the help.

Additional:

Here is an updated sample of the code, but again, it won't pick the rows I need based on the "True" criteria of cell H

Dim cell
Dim targetrange As Range
i = ActiveCell.Row
Set targetrange = Range("B" & i & ":D" & i & ", G" & i)
For Each cell In Range("H7:H14")
If cell Then
cell.Select
targetrange.Cells.Select
Selection.Copy
Sheets("archive").Select
ActiveSheet.Paste
End If
Next cell
End Sub


***THANKS FOR SETTING ME STRAIGHT MARK! ( I told you I was a novice...I will drop these tags in from now on!!***) :-) :-)
It just picks the active row that was last selected.

[GTO]

Totally aside, would you please start using the vba code tags? Such as:

'...Your code here...

Whilst imperfect (line-continuation falls over), the tags just make your code so much easier to read. You can just click the little green/white 'vba' button to auto-insert these, and plunk your code between :-)

Mark

[Teeroy]

You've got TRUE and FALSE as text in the column H. The TRUE and FALSE I was referring to are Boolean values equivalent to integer 1 and 0 respectively in Binary. You can only use "TRUE" and "FALSE" in VBA to indicate 1 and 0 states (think of them as constants). Change the column H values to 1 and 0 and the following should work.
Note that this code will copy the line to the bottom of the list on the archive sheet. You could use copy and delete (in as commented line) to remove the Task from the first sheet or cut and paste.

Dim cell
Dim targetrange As Range
For Each cell In Range("H7:H14")
If cell Then
i = cell.Row
Range("B" & i & ":D" & i & ", G" & i).Cells.Copy Sheets("archive").Range("A"& rows.count).end(xlup).offset(1,0)
'cell.entirerow.delete
End If
Next cell
End Sub

[GTO]

***THANKS FOR SETTING ME STRAIGHT MARK! ( I told you I was a novice...I will drop these tags in from now on!!***) :-) :-)

You are most welcome and thank you for catching my tiny input.

AussieBear will be thrilled! (PLease forgive the joke/intrusion, a slight 'harass', as I noticiced that Ted (AussieBear) has been back online recently. :hi:

[newbie10]

Cheers Teeroy,

Thanks again for the help. I was able to implement everything, and made my clear button work like a charm.

I'm eternally appreciative for all the help from all the users. I don't want to push my "novice" luck, however, my boss then says, "hey, why don't, instead of archving it, export it to a .csv file, and we can put it into a database......arrrrggggggg. So here it is:

My current code:

Sub clear_archive_task()
Dim taskrange As Range

Dim cbox As CheckBox
Dim cbrng As Range

With ActiveSheet
For Each cell In Range("I7:I14")
If cell Then
i = cell.Row
Range("b" & i & ":D" & ", G" & i).Cells.Copy Sheets("Archive").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Set cbring = Range("e" & i).Cells
For Each cbox In ActiveSheet.CheckBoxes
If Intersect(cbox.TopLeftCell, cbrng) Is Nothing Then
Else
cbox.Delete
End If
Next cbox
cell.EntireRow.Delete
End If
Next cell
End With


I would like to take the information in cells "B", "C", "D" and "G" and export it to a .csv file with the headers "B" = Task, "C" = Date assigned, "D" = Date Due "G" = Update, and a cell in the csv file that time stamps the import for "Completed" date. I was also hoping that I could pull the "name" of the activesheet and drop it into a header as well. I know it's a lot, and I hope it's not too much to ask. Please help if you can. Thanks so much in advance.