Visual Basic :: Excel Help - First Empty Cell

Excel Cell Value Is Not Empty
Hello,

Sometimes I do not understand simple things but maybe somebody else. I'am testing a cell value but I do not understand why the if statement is true:

if (IsNumeric(trim(wshSubnet.Cells(i, wshSubnet.Range("DBB_CB_NAME"& i).Column))) and _
(cint(trim(wshSubnet.Cells(i, wshSubnet.Range("DBB_CB_NAME"& i).Column))) > iCountInstal )) Then

Now: the value of the Cell is 2; iCountInstal = 2
DBB_CB_NAME"& i is a cell name

What I do not understand how can this be True? Because in my opinion the the value of the cell is 2 and is not bigger than iCountInstal which is 2 as well. The same problem do I have when the value of iCountInstal = 1
I think I make somewhere a stupid mistake but I don't see where

Must I do a type conversion first?

Nico

Highlight Empty Cell In Excel
What is the VB code I should use to browse through a database in a spreadsheet, and then hilight any cells that are blank?

Thank you!

Excel Programming: Empty Cell Problem
Hey guys.
I've got a bit of a problem on my spreadsheet (an A-level ICT project, where we have to basically make a functional program in excel).

Lets say I label a cell 'username'. This is a user input box, and I'll print that same information on a different sheet in the same workbook.
However, the problem is, that when the user doesn't enter anything, it displays 0. I don't want this to happen.
Therefore, would it be possible to display a dialog box if the user left the cell blank? Or is there any other solutino that you could offer?

Thanks for your help.
-aJ

Howto Find Next Empty Cell In An Excel Worksheet?
Hi!

I'm making this program that writes to an excel file to keep a summary of information. I used the Excel object library as a reference and I can open the excel file.

My problem is how can I find the next empty cell in a column so I know where to append the next information?

Hope someone can help....

Thanks!!

VBA: Excel Macro: Copy Formula Down In Sheet2 Until Cell In Sheet1 Is Empty
I am trying to modify a soon to be recorded macro in Excel. I receive a file every month with X number of rows. I need to copy a formula that references a cell in Sheet1 down Sheet2 until there are no more rows to copy in Sheet1 (until the row to be copied is empty).

I found Do ... Loop Until, but I can only get it to work by testing a cell on the same sheet b/c the example I found uses ActiveCell.
Code:

Do
ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 1))

How can I test the cell on another sheet that's in the same row as ActiveCell?

I'm not asking for a handout, but they're always appreciated

Cannot Distinguish An Empty Cell From A Cell Having A Value Of 0
guys i need some help on this one,
i am comparing two cells in excel, however
if the value of the first cell is 0 (zero) and
the other cell has no value the resulting
value is true...
meaning comparing 0 and an empty cell evaluates to true,
how is this possible, how can i make this expression
to evaluate to false???

hanks.

Last Empty Cell
What I want to do is to simply take the data from the last cell, copy it and then paste it 5 cells down. If I use the code below the computer hangs every time. What am I doing wrong?

Code:

Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).Row

Range(Cells).copy

Range(cells + 5).Paste

Next Empty Cell
Hey guys and gals...

should be simply but not working too well...

I have UserForm1 with 5 info boxes accross

that is the same 12 times down the form..

All I am trying to do is write info from the UserForm (on button click) to the next empty cell from a starting row...

Get A Next Empty Cell
I have a prog using which I can open an existing Excel file and then send the date from it to new created Excel file. But here I have a problem on passing to another cell.
I mean how can I go to the next empty Row after I entered the date?
Let's see an example:
The first time when I click on SendButton the date will be placed on new created file in A1,B1,C1,D1,....est. the next time I will send the date I need it to go to A2,B2,C2,D2,....est.

How can I learn the next empty Cell

Private Sub SendButton_Click()

Like what steps I need to go here to learn
the next empty row

ws.Cells(1, 1) = txtName.Text
ws.Cells(1, 2) = txtAmount.Text

End Sub

Any idea appreciated!

VBA Sub To Delete A Row If A Cell Is Empty
Hello Iam a newbie VBA user.
I have a spreadheet with several rows. Some of teh cells within the rows are blank. I am trying to write a subroutine that will look for a specif cell in a row (say B2) and if it is blank delete the entire row.

The code below will work if a ROW is empty but I am trying to refine it such that I select a range of Cells and if the cell is empty delete the entire row

Sub DeleteEmptyRows()
LastRow = ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.WorksheetFunction.CountA(Rows(r)) = "" _
Then Rows(r).Delete
Next r
End Sub

Any suggestions?

Finding The Last Empty Cell
I'm struggling to find an empty cell. I'm using:

Code:
intRow = Cells.Find(What:=IsEmpty(),LookIn:=xlValues,SearchOrder:=xlByRows)
I need to know what to put in the IsEmpty bit.

Any ideas?

Fill Next Empty Cell
Hello Everyone,

I have read alot of posts on this topic but I couldnt find one that worked for me, hopefully someone can help me out.

I have a userform in MS Excel in which when a user presses the command button it will fill in data on my WorkSheet1. The data that will be entered is Company Name, Customer Name, Address, Address2, City-State-Zip.

If every field had data in it when the user hits the command button then it would look something like this below on the worksheet:

Cell ("C11") = Company Name
Cell ("C12") = Customer Name
Cell ("C13") = Address
Cell ("C14") = Address2
Cell ("C15") = City, State Zip

However sometimes the Address2 field (which mainly has P.O. Boxes) is empty cuz not every company has a P.O. Box.

When this is the case I would like all the data below this to shift up and fill in the void for that empty field. Same goes with any other field that does not contain any values when the user hits the command button.

Any help is greatly appreciated,
Kind Regards

How To Determine Is A Cell Is Empty?
Hi Everybody

Since the function IsBlank() doesn't work with VBA, is the only way to test whether or not a cell is emtpy is to either of these (shown below) or is there is a built-in Excel function in VBA equivalent to IsBlank().

Code:
'Assume that cell(A1) on the Sheeet1 is blank - nothing has been entered in it yet.

Sub testing()
Dim myrange As Range
Set myrange = Worksheets("Sheet1").Cells(1, 1)
Dim test1 As Variant
test1 = myrange.Value 'test1 returns Empty
Dim test2 As Boolean
test2 = IsEmpty(Worksheets("Sheet1").Cells(1, 1).Value) 'test2 returns True
End Sub

Best regards

Deepak Agarwal

To Select An Empty Cell
In order to select a value before a blank cell, I get this command from other topic:

myvariable = Range("C5").End(xlDown).Row

unfortunately if the cells contain a formula like this:

=IF($C55="";"";........)

the above command not recognize no empty cell until that do not end the formulas introduced

In few word I need to modificate the command, inserting a control if cell is empty, or something like this, and not if is blank.
Someone could write to me this simply (I hope) solution ?
Thanks to everybody.

Empty Cell Verify...
Need to verify that a cell contains no text or value before a VB routine over-wrights it.

I need a little help here, most of the folks that are acquainted with me say that I’m beyond help. All of the cells contain formatting, but none contain text or values on the test sheet, every time the error trap routine (If Not ("C" & RW) = "" Then GoSub FullRoutine) runs it tells me that the cell contains data but isblank() returns true. This is way beyond my comprehenson, haven’t scrached code in a lot of years, usually just record it and hope for the best. But need a little something more here.

Sheets("new").Select
Range("A31:J36,B33,B34,A36").Select
Range("A36").Activate
Selection.ClearContents
Range("A27").Activate
RW = Range("A27") + 1
Range("A36") = RW
Sheets("2005").Select
Range("B" & RW).Select
Range("B" & RW).Activate
If Not ("B" & RW) =””Then GoSub FullRoutine
Range("C" & RW).Select
Range("C" & RW).Activate
If Not ("C" & RW) = "" Then GoSub FullRoutine
Range("A27:I27").Select
Selection.Copy
Sheets("2005").Select
Range("A" & RW).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Stop
FullRoutine:
Sheets("new").Select
Range("A33").Select
ActiveCell.FormulaR1C1 = "STOP"
Range("B33").Select
ActiveCell.FormulaR1C1 = "Are You Sure Someone's not in this unit?"
Range("B35").Select
End
End Sub

Nex Empty Cell In Range
If I have the range A1:A65536, how do I search that range and activate the next empty cell below within the range ?

Paste To An Empty Cell
My question must have been answered before... but I just can't find the thread...

how can VB paste into an empty excel cell, without producing the "paste method out of range" error?

-jb

How To Avoid Empty Cell?
Hello, Everyone,
If there is some empty cell in 18 column, it won't return the last Row number. Is there a way to solve it?
Thank you very much!
Charlie

Code:
nLastRow = xlApp.Worksheets("sheet1").Cells(65536, 18).End(xlUp).Row

Detect Empty Cell
I'm writing a macro ( actually 2 macros, one for character, one for numeric ) that allows the user to override an exsiting value calulated elsewhere in the spreadsheet. As a first step, I want to detect when/whether the user has entered a value or not.

I thought that something like

mycheck = IsEmpty(Override)
If mycheck = -1 ( or NULL ) Then .....

would tell me that when the user DID NOT enter something ( or had deleted what he did enter ). However, even when the cell is empty ( and confirmed by ISBLANK in Excel ), I can't detect when the cell is empty.

hope this isn't a really stupid queston .

thanks in advance for your help

regards
woody

Empty Cell Test
Guys,
How would you test a column for a empty cell, then once you have found your empty cell, mark that as being empty then go back up one and select from top of the column to the variable defined bottom of that column. Any help would be appreciated. Thanks!

Empty Cell In Graph
Hello,
Facts:
Column A are X axe of a graph. Column B is the Y axe of the graph.
If the cell B3 equal zero or some texte, the graph will show a point at zero. But if B3 is empty, the graph will not show the point at all.
Question?
My cell B3 contain a formula, which return "" if false. Excel recognize "" as empty cell, but the graph show a point at zero! I would like either:
-to find a way that the graph do not show the point when the cell got "" as value.
-to find a way in the formula to return an empty cell (carriage return?)
I have already tried to play with the graph options with no luck. I am trying not using VBA.
Thanks in advance,
Chib

How To Emulate A Really EMPTY Cell?
Hi folks,

I can't find a solution for my problem. Excel treates empty cells very special and I want to have this behavior for cells containing following function:

Public Function NumOnly(r As Range) As Variant
' r referes to cells containing numbers of any kind -> return this number
' otherwise make the cell appear empty
' (event to Excel: e.g. Charts or AVERAGE)
Dim c As Range ' For Each c

Application.Volatile ' always recalculate

For Each c In r.Cells
Select Case VarType(c.Value)
Case vbInteger, vbLong, vbSingle, vbDouble ' numbers only
NumOnly = c.Value
Case Else ' otherwise empty
' NumOnly = CVErr(xlErrNA) ' #NV doesn't do what I want
' Set c.Value = Empty ' this in turn doesn't work
End Select
Next c
End Function

It is not enough to return "" because charts would just draw a value of 0 and AVERAGE would sum up this cell, too. The porblem is that the cell can't be empty as such, since it contains a formula (e.g. "=NumOnly($A$1)"). But how can I make Excel behave like it was empt? With the #NV-solution I could get by (sort of) but line charts wouldn't leave a gap where the empty cell should be and AVERAGE would return #NV, too.

Please help

AndyR

Giving A Cell An 'empty' Value
I want to include an IF statement in one of my cells and I want to have an 'empty' value, that is, the same as if i had just pressed delete to wipe the contents of that cell. How can I integrate this into my EXCEL formula?
Thanks,
Rob

Find First Empty Cell
I am using VB to open a worksheet and find a value within a cell. From there I need to read every cell until I get to the first blank cell. If I know the column and row to start from in question how can I find the first row in that column that is empty.

How To Determine If A Cell Is Empty
I'm attempting to create a macro that will delete an entire row if the gift
amount is less than $99 and the comment field is blank.

'Delete row for all gifts under $100 and comment field is empty
giftcount = 1
Set xRng = Selection
Range("A2").Select
For giftcounter = 2 To FinalRow Step 1
If Cells(giftcount, 89).Value <= 99 Then
If Cells(giftcount, 96) = "" Then
xRng.Cells(giftcount).EntireRow.Delete
Else
giftcount = giftcount + 1
End If
Else
giftcount = giftcount + 1
End If
Next giftcounter

Thanks,
Dorie

********************************************
"Success is to be measured not so much by the position
that one has reached in life as by the obstacles which he
has overcome while trying to succeed.
-- Booker T. Washington

Some Help Needed {check If Cell Is Empty}
Hi.

I'm new here. I hope this is the right place to put this.

I am very new to visual basic, and the one I'm using atm is the one from Excel 97.

This is the code I need hlp with.

If Range("D7") Then

I have no idea how to say this but what I want is basically, if the cell D7 has any kind of text/numbers etc. then i want it to etc. The trouble is I don't know what to put inbetween Range D7 and Then to say if the cell has any text/numbers.

Can anyone help me please.

Selecting A Range {until Empty Cell}
Hi

I'm trying to write code that will select a range of cells (in column A) but will stop selecting once an empty cell is detected.

I'm not sure if this code should include a do-until loop?
I would greatly appreciate any help.
Thanks

Making A Do While Loop Until Empty Cell
How do i make a do..while.. loop until it reaches an empty cell in column x?

thanx

Empty Cell/clear Row If Doesn't Contain #
Hi everyone,

I'm looking for some help here. I have an excel worksheet and I'm trying to find out if this is possible.

I need to have Column C checked to see if the cell contains a "#", if not to empty the row.

Then I need to check again if Column C has empty cell, to delete that entire row.

I need to do the empty Colum C cell first because some rows with only Column A filled with text (no #) need to stay put.

Is this possible? Can someone with some macro knowdledge kindly lend a hand?

Thanks so much in advance!

Error If Reading Empty Cell.
When ever my program hits a cell that is empty in my database, it crashes and says "invalid use of Null."

Anyway for me to keep it going?

Or am I going to have to redesign my database to fill every cell?

Need Help To Find The Last Non-empty Cell In A Column Dynamically
Hi!
I need to get the value in the last filled cell of a column. My cells in the column have values - either TRUE or FALSE or <blank>. I need to get the last cell value in the column (only the TRUE and FALSE, I don't need to take in the blanks).

Column NumberValue
C27FALSE
C28FALSE
C29TRUE
C30FALSE
C31FALSE
C32FALSE
C33FALSE
C34
C35

Does anybody have any suggestions?

There's one more thing. The values are in Column C (let's say). And whenever there is a change in the worksheet, the cells keep being filled downwards. Now, I need to get the last value of the column and put it in say J2. This cell J2 should keep getting updated with the latest value. I don't want to have the formula repeated downwards. The last
filled value in the cell (which keeps changing dynamically - the ranges change dynamically) should be updated in cell J2.

For example, first the cells C2 to C5 are filled with True or False. The rest of the cells are <blanks>. So, I want the value of C5 to be in J2.

Next, the cells filled will be C2 to c8. Then I need the value of cell C8 to be in J2.

Let me also tell you that Cells in column C are filled based on the formula - =IF(G23<>" ",AND(G23=1,F23=0),"")

Thank you!

Davy

Avoid Date Type Cell Empty?
Hello, Everyone:
My data cell is date type string. When it is empty, the default is 12Am. How can I avoid? It is possible for me to replace it visit most recent time in which cell is near it? Any good suggestion to program it in VBA? Is there any way to avoid this default value?
Thank you very much!
Charlie

Cell.Find Returns Error When EMPTY Is Detected
Hi there, I am trying to do a looping. On the user front, it is supposed to perform a find cell containing 'Box ID' and from there, delete fixed no. of rows from the top. However problem arises when find returns EMPTY coz there is no more cell containing 'Box ID'. How to I troubleshoot this error? Thanks for your help in advance!

Code:
indResultA = Cells.Find(What:="Box ID", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _False, SearchFormat:=False).Activate

Do While findResultA = True

Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(-10, 0)).EntireRow.Select

Selection.Delete Shift:=xlUp

'Macro detects error if no more cell is found and error is found in VB.
findResultA = Cells.Find(What:="Box ID", After:=ActiveCell, LookIn:=xlValues, LookAt _:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _False, SearchFormat:=False).Activate

Loop

Edit by Moderator:
Please post Excel questions, in the Excel forum.

Please use the [vb][/vb] tags when you post your code. Edit or reply to this post to see how.

Thank you.

Replace DB Null So That HTML Page Creates An Empty Cell
I use this code to convert a DB table to an HTML page but the only way I can get it to create a cell where the returned value is NULL is to use the "_", but I just want an empty cell. If I use "" then the cell is not drawn on the HTML page. What can I substitute instead of the "_"?
Code:

Private Sub Form_Load()
Dim oConn
Dim rs
Dim fso
Dim outhtml
Set fso = CreateObject("Scripting.FileSystemObject")
Set outhtml = fso.CreateTextFile("c:userdumpChoice_Report.htm", True)

outhtml.WriteLine "<html>"
outhtml.WriteLine "<body>"
outhtml.WriteLine "<table border=1>"

Set oConn = CreateObject("ADODB.Connection")

oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
            "Dbq=C:Documents and SettingsJulianMy DocumentsNHDSChoiceChoice.mdb;Uid=Admin;Pwd=choice2openZZZ;"

Set rs = oConn.Execute("SELECT * FROM tblClient")

  outhtml.WriteLine _
        "<tr><td>" & rs.Fields(1).Name & "</td><td>" & _
                        rs.Fields(2).Name & "</td><td>" & _
                        rs.Fields(3).Name & "</td><td>" & _
                        rs.Fields(4).Name & "</td><td>" & _
                        rs.Fields(5).Name & "</td><td>" & _
                        rs.Fields(6).Name & "</td><td>" & _
                        rs.Fields(7).Name & "</td><td>" & _
                        rs.Fields(8).Name & "</td><td>" & _
                        rs.Fields(9).Name & "</td></tr>"
Do While Not rs.EOF
  outhtml.WriteLine _
        "<tr><td>" & IIf(IsNull(rs.Fields(1).Value), "_", rs.Fields(1).Value) & "</td><td>" & _
                        IIf(IsNull(rs.Fields(2).Value), "_", rs.Fields(2).Value) & "</td><td>" & _
                        IIf(IsNull(rs.Fields(3).Value), "_", rs.Fields(3).Value) & "</td><td>" & _
                        IIf(IsNull(rs.Fields(4).Value), "_", rs.Fields(4).Value) & "</td><td>" & _
                        IIf(IsNull(rs.Fields(5).Value), "_", rs.Fields(5).Value) & "</td><td>" & _
                        IIf(IsNull(rs.Fields(6).Value), "_", rs.Fields(6).Value) & "</td><td>" & _
                        IIf(IsNull(rs.Fields(7).Value), "_", rs.Fields(7).Value) & "</td><td>" & _
                        IIf(IsNull(rs.Fields(8).Value), "_", rs.Fields(8).Value) & "</td><td>" & _
                        IIf(IsNull(rs.Fields(9).Value), "_", rs.Fields(9).Value) & "</td></tr>"
  rs.MoveNext
Loop

outhtml.WriteLine "</table>"
outhtml.WriteLine "</body>"
outhtml.WriteLine "</html>"
outhtml.Close

End Sub

|
+--JDMils
|
+--VB6
+--VB Dot Net
|
+-- Navman GPS Forums @ http://forum.jdmils.com
|

Edited by - JDMils on 5/11/2007 2:03:41 AM

Excel - Making A Cell Equal To The Value Of The Formula In The Cell
I thought I'd seen something on this already, but I coudln't find it. My appoligies if I'm creating a duplicate thread.

I'm writing a macro in excel to fill in data in a database. Unfortunately, my titles for each entry have to be taken from multiple cells and put into one cell. The code to place the title in the title cell is:

Code:Dim title As String
    title = inputname & "!$B$3, " & inputname & "!$C$3, " & inputname & "!$D$3, " & inputname & "!$E$3, " & inputname & "!$F$3, " & inputname & "!$G$3, " & inputname & "!$H$3, " & inputname & "!$I$3"
    title = "=CONCATENATE(" & title & ")"
    ActiveCell.Value = title

It works fine. The problem I'm having is that every time I save and reopen my database excel pops up with this "The workbook you opened contains automatic links to information in other workbooks..." It's getting very annoying. And it also gives me problems if anybody touches the already filled title cells. I was thinking that there might be a way to make the formula equal to the value of the formula. For example making a cell have the value 4 instead of the forumula "=2+2". Thanks for any help.

How To Link An Excel Cell To An Access Table Cell
I am trying to import an populated excel form into access using just specific cells (where the data is located) only. I am able to open excel and get the data from the cells but how can i assign them back to my access table? This is like importing a populated excel form into access database.

I used these to get my excel info

Set excel_app = CreateObject("Excel.Application")
excel_app.Visible = False
excel_app.Workbooks.Open FileName:=mypathname & myfilename
Set excel_sheet = excel_app
x= excel_sheet.cells(10, 4).Value 'To get the excel cell value'
y= excel_sheet.cells(12,4).Value

Now I want x to be assigned to table "AppendTable" in column "AppColumn1"
and y in column "AppColumn2"

help!!!

How To Read An Excel Spreadsheet Column, That Is, Read From The Top Cell To Bottom Cell (like File R
How to read an excel spreadsheet column, that is, read from the top cell to bottom cell (like file read to eof)? how can I know which cell is the last cell?

thanks

How To Differentiate A "0" (zero) Cell Vs. Empty?
I am coding conditional statements based on whether a cell is empty or is zero, but they seem to be equivalent.

Can anyone tell me how to check for "0" which is different than empty?

I Am Exporting An Access Query To Excel, How Can I Define The Excel Cell Size/format
I am using TransferSpreadsheet to Export a Query to Excel with a button from Access.

How can I define in Excel when I export it, the size of the cells, the type of letter (Arial, Bold), The Background color.

Private Sub Impacto_Click()
DoCmd.TransferSpreadsheet transfertype:=acExport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
TableName:="Impact", _
Filename:="C:Impact.xls", _
hasfieldnames:=True
End Sub

Find Location Of A Cell In Excel-import From Excel Into Access
Hi
I want to import some columns from an excel spreadsheet into an access table using vb6.

I am going abt this by finding the location of the cell but am having problems this way

Code:
Private Sub ImportExcelAttend()
Dim imA As New ADODB.Recordset 'Recordest for temp table
Dim ex As New Excel.Application
Dim I, J, a As Integer
Dim TheFile As String

imA.Open "select * from tbl_tempAttend", MyConn, , adLockOptimistic

ex.Workbooks.Open txtLocation.Text
ex.Cells(I, J) = "%att"

however i cannot get it to work. any help this way would be appreciated.

OR if you know another way to import these few colums with unknown location of cells into access i would be very grateful. Location will always change every time.

Regards
JohnnyH

How To Shade Cell In Excel Based On The Date Value In The Excel Column
Hai ,

I export the content of the MSHFlexgrid to ExcelSheet , Here with i have attached my Excel sheet. The below is the Code i use to export the Excel Sheet. If you see Excel Sheet attached you can find the cells shaded. The code is working fine,

I need a alteration in it. I need to shade the Cell based on the Value in the Column DOffStrm,

if the value in that column is below 30 and the T & I Ets/Ospas date begins with 1 (ex: 3/01/2007)
then the shade should be inside one cell,

suppose the Column DOffStrm is 30 and the Date value of T & I Ets/Ospas is 3/15/2007 then shade should start from half of the current cell and end in the second half of the next cell.

Kindly check this and tell me.

Code:

VB Code:
Private Sub cmdExport_Click() Dim MDur As Integer Dim obj1 As New Excel.Application Dim wsheet As Worksheet Dim wbook As Workbook Screen.MousePointer = vbHourglass Set wbook = obj1.Workbooks.Add Set wsheet = obj1.Sheets(1) Dim i% Dim j% Dim Ce% For i = 0 To MSHFlexGrid1.Rows - 1 For j = 0 To MSHFlexGrid1.Cols - 1 If j > 7 And j < 55 And i > 1 And Len(MSHFlexGrid1.TextMatrix(i, j)) > 1 Then MDur = MSHFlexGrid1.TextMatrix(i, 55) MDur = MDur / 30 wsheet.Cells(i + 2, j + 1).Value = Format(MSHFlexGrid1.TextMatrix(i, j), "dd") wsheet.Cells(i + 2, j + 1).Font.Bold = True obj1.ActiveSheet.Cells(i, j).Font.Color = vbRed obj1.ActiveSheet.Cells(i + 2, j + 1).Interior.Color = vbYellow For Ce = 0 To MDur Next ElseIf j > 7 And j < 55 Then wsheet.Cells(i + 2, j + 1).Value = MSHFlexGrid1.TextMatrix(i, j) wsheet.Cells(i + 2, j + 1).Font.Bold = True Else wsheet.Cells(i + 2, j + 1).Value = MSHFlexGrid1.TextMatrix(i, j) End If Next Next For i = 0 To 1 For j = 0 To MSHFlexGrid1.Cols - 1 wsheet.Cells(i + 2, j + 1).Font.Bold = True wsheet.Cells(i + 2, j + 1).Font.Color = &H800000 Next Next Application.DisplayAlerts = False With obj1.Sheets(1).Range("H2:S2") .Select .Merge End With With obj1.Sheets(1).Range("T2:AE2") .Select .Merge End With With obj1.Sheets(1).Range("AF2:AQ2") .Select .Merge End With With obj1.Sheets(1).Range("AR2:BC2") .Select .Merge End With obj1.Rows(2).HorizontalAlignment = Excel.xlCenter obj1.Columns.AutoFit Screen.MousePointer = vbNormal obj1.Application.Visible = True End Sub

also attached another excel file call Sheet, this type of shading is required here as in the Book1 which is attached here,

so i have can have the copy of the same picture in 4 different Width,

1st picture width will be of 24 and
2nd will be 50,
3rd will be 75
4th will be 100

so with this 4 different images i can cover the area in the cell,

the only code now needed is

1. how to place the Picture in the ExcelSheet through VB.6

2. how to specify the starting part (or X axis or Cell Range in Excel Sheet) of the picture to be placed

i hope, if i have the code for the above two, then it will be solved. Kindly check this and reply me.

Thankyou,
Chock.

How To Shade Cell In Excel Based On The Date Value In The Excel Column
Hai ,

I export the content of the MSHFlexgrid to ExcelSheet , Here with i have attached my Excel sheet. The below is the Code i use to export the Excel Sheet. If you see Excel Sheet attached you can find the cells shaded. The code is working fine,

I need a alteration in it. I need to shade the Cell based on the Value in the Column DOffStrm,

if the value in that column is below 30 and the T & I Ets/Ospas date begins with 1 (ex: 3/01/2007)
then the shade should be inside one cell,

suppose the Column DOffStrm is 30 and the Date value of T & I Ets/Ospas is 3/15/2007 then shade should start from half of the current cell and end in the second half of the next cell.

Kindly check this and tell me.

Code:Private Sub cmdExport_Click()
Dim MDur As Integer
Dim obj1 As New Excel.Application
Dim wsheet As Worksheet
Dim wbook As Workbook

Screen.MousePointer = vbHourglass

Set wbook = obj1.Workbooks.Add
Set wsheet = obj1.Sheets(1)
Dim i%
Dim j%
Dim Ce%

For i = 0 To MSHFlexGrid1.Rows - 1
    For j = 0 To MSHFlexGrid1.Cols - 1

        If j > 7 And j < 55 And i > 1 And Len(MSHFlexGrid1.TextMatrix(i, j)) > 1 Then
            MDur = MSHFlexGrid1.TextMatrix(i, 55)
            MDur = MDur / 30
            wsheet.Cells(i + 2, j + 1).Value = Format(MSHFlexGrid1.TextMatrix(i, j), "dd")
            wsheet.Cells(i + 2, j + 1).Font.Bold = True
            obj1.ActiveSheet.Cells(i, j).Font.Color = vbRed
            obj1.ActiveSheet.Cells(i + 2, j + 1).Interior.Color = vbYellow
            For Ce = 0 To MDur

            Next
        ElseIf j > 7 And j < 55 Then
            wsheet.Cells(i + 2, j + 1).Value = MSHFlexGrid1.TextMatrix(i, j)
            wsheet.Cells(i + 2, j + 1).Font.Bold = True
        Else
            wsheet.Cells(i + 2, j + 1).Value = MSHFlexGrid1.TextMatrix(i, j)
        End If
    Next
Next

For i = 0 To 1
    For j = 0 To MSHFlexGrid1.Cols - 1
        wsheet.Cells(i + 2, j + 1).Font.Bold = True
        wsheet.Cells(i + 2, j + 1).Font.Color = &H800000
    Next
Next
Application.DisplayAlerts = False
With obj1.Sheets(1).Range("H2:S2")
    .Select
    .Merge
End With

With obj1.Sheets(1).Range("T2:AE2")
    .Select
    .Merge
End With

With obj1.Sheets(1).Range("AF2:AQ2")
    .Select
    .Merge
End With

With obj1.Sheets(1).Range("AR2:BC2")
    .Select
    .Merge
End With
obj1.Rows(2).HorizontalAlignment = Excel.xlCenter
obj1.Columns.AutoFit
Screen.MousePointer = vbNormal
obj1.Application.Visible = True
End Sub

Thankyou,
Chock.

Get Last Non-empty Row Number In Excel
Hi all.
I'm trying to get the number of records that I exported from excel to a recordset. I have used .RecordCount attribute of the recordset and it gave me the number of rows successfully exported.
However, if i try to edit the excel file that I want to export, by deleting the contents of the cells by highlighting them and pressing delete, the number of records that will given through the .RecordCount attribute is still the original one.
But if i delete the rows by highlighting the rows themselves and clicking the Delete Rows command, the correct record count is given.
My problem is that, I protected the worksheet so there's only a limited action that can be done to the worksheet and deleting rows is not one of them.
How will I get the correct total number of records then?

Thanks all for always addressing my concerns...

'Empty' Character In Excel
I would like to check(detect) for an 'empty' cell in excel from vb but i cant.
What is that 'empty' character ?
If xlsheet.Cells(intRowFr, 1) = "" then
.
.
.

Regards
Goh

VBA: Finding Next Empty Row In Excel
Jocelyne,
Your macro that finds the next empty row in Excel sounds slick. Right now I
am using a fix in a program that uses an 'end of file' marker (that I write
when creating the data file off a mainframe) in order to find the end of the
Excel worksheet data. I would much rather use your solution. If you still
have this code around will you resend it? For some reason the .bas file
wasn't attached on the email that I received from the group list, below.
Thanks
Jean

Delete Empty Excel Sheets
hello,
i have a program that automates excel from vb. i was just wondering what would be the easiest way to delete any sheets that have no data in them when a workbook is loaded.

thanks for the help

Determining If Excel Cells Are Empty Or Not
What would be the code i need for determining if cells are empty or not in excel?

The Memory Not Goes To Empty After Using Excel Objects
Why when I exit the prog which have reference to Excel object and having the code in Sub Form_Unload I try to close all the objects, the memory not goes to empty?
Where I am missing?

General decleration is here

Dim xl As Excel.Application

Dim wb As Excel.Workbook

Dim ws As Excel.Worksheet

Private mExcel As Excel.Application

Dim mWorkbook As Excel.Workbook

Dim mSheet As Excel.WorksheetPrivate

And Exit from the prog is here :

Sub Form_Unload(Cancel As Integer)

On Local Error GoTo Close_Error

xl.Quit

wb.Close

mExcel.Quit

mWorkbook.Close

Set xl = Nothing

Set wb = Nothing

Set ws = Nothing

Set mExcel = Nothing

Set mWorkbook = Nothing

Set mSheet = Nothing

Close_Error:

End Sub

Thanks in advance