# Posts by skamat

• ## Select Blank Cell in a Range

Re: Select Blank Cell in a Range

Thanks RoyUK for the code.
I had put the code in the formula and it does calculate very well.

But the problem is that for formula calculates till Row 1111.
Wherein it should calculate till the end of the row 5 where the data ends.

Please see the attached sample file.

• ## Select Blank Cell in a Range

Re: Select Blank Cell in a Range

Sorry RoyUK I apoligize my mistake, I did not try your code.

Both the codes work very well

Thanks and sorry

• ## Select Blank Cell in a Range

Re: Select Blank Cell in a Range

I need to select the blank cell for formula.

If that is not possible as the cells are blank then I have something in my mind that I am not able to figure it out how to go about would appreciate if helped.

Idea is to select range D10 : I15

My thought is

1. Finding the number of columns from D8 to End of the row (In this case is I8). The number of counts would give 5 (column E8, F8,G8, H8, I8)

2. Then counting number of rows from row number 10 to end from C10 to end (In this case the counts would be 5) (row 11, 12, 13, 14, 15)

3. Now we can apply the formula

Code
``set aa =  Range ("D10").offset(5)``

This will select cell D15 , As we know the number of rows is "5" so offset is 5, as per my idea in point 2

Then another code

Code
``set bb = range (aa.address).offset(, 5)``

Same here as we know the number of columns is "5" so offset is 5, as per my idea in point 1

That should select cell I15

This can be then put in my formula as below

Code
``Range("D10:"  &  bb.address).select``

Hope I am able to express myself properly

• ## Select Blank Cell in a Range

I want to select a Blank Cell in a range

Please see the attached sheet. In the sheet I would like to select Cell I15.

I am aware of selecting with the formula

Code
``Range("C15").Offset(, 6).Select``

But what I need is to select with XL(end) formula.

I have done some work that can be seen in the module, however I am even placing my requirments here.

• ## Merge Cells based on used cells in column 5

Re: Merge Cells based on used cells in column 5

I had forgotten the attachment and then edited the post to attach the sample sheet.

Project requires megring to give a neat presentation.

• ## Merge Cells based on used cells in column 5

Re: Merge Cells based on used cells in column 5

Please see the attached sample sheet

In row 7 there are Zones mentioned from Zon1 to to Zone 5

Now the merged cells should be C4:G4

When new Zones are added for Eg. Zone 6 and Zone 7 in col. H and I

then the merged cells should be C4:I4

• ## Merge Cells based on used cells in column 5

I have data written in Cells B5, C5, D5 etc that changes as you add more column data

I am trying to figure out how to merge cells starting from Cell B3 based on used cells in row B5 to end of column.

• ## Pickup Values from Cells to Combobox in user form

Re: Pickup Values from Cells to Combobox in user form

I cannot get it right could you please show how to do it

• ## Pickup Values from Cells to Combobox in user form

Attached is the sample sheet.
I require the combobox to pickup values from sheet1 cells A7:A11, and whenever any value changes from A7:A11, the same should be reflected in combobox.

Further one more list value should be added in combobox "Select Data" This is seperate and should only appear in combobox and not in sheet1

• ## copy and remove duplicates

Re: copy and remove duplicates

found a smaller code

Code
``````Sheet2.Range("C5", Sheet2.Range("C5").End(xlDown)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheet2.Range("A1"), Unique:=True
Sheet2.Range("A1", Sheet2.Range("A1").End(xlDown)).Cut
Sheet1.Range("C7").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheet1.Range("C7:C" & Range("C" & Rows.Count).End(xlUp).Row).RemoveDuplicates Columns:=1, Header:=xlNo``````
• ## copy and remove duplicates

Re: copy and remove duplicates

your code works perfectly fine with minor modification needed

If you see sheet1 has letter "a" appearing 5 times and after your code is run the letter "a" appears 2 times in sheet2 instead of 1 time.

I tried with different alphebets writing 2 times and same error occured it shows up 2 times in sheet2 instead of 1 time

• ## copy and remove duplicates

Re: copy and remove duplicates

code does not work, please see the sample sheet wherein i tried your code

• ## copy and remove duplicates

there is a long list of data in column C sheet1
how can i copy that column to sheet2 column C by removing duplicate entries

The code that I have is

Code
``````sheet1.Range("C5" & sheet1.Range("C" & Rows.Count).End(xlUp).Row).Copy
sheet2.Range("C5").pastespecial
sheet2.Range("C5:C" & Range("C" & Rows.Count).End(xlUp).Row).RemoveDuplicates Columns:=1, Header:=xlNo``````

What happens is that if I am having 1500 or more entries, the entire 1500 entries is first copied and then duplicates removed.

What I am looking for is a fast VBA code to copy but not duplicates so that 1500 entries are not copied but only single entries.
The origional sheet1 entry should not be changed.

• ## Sum to start to end of the row

Re: Sum to start to end of the row

I have long list of data that changes frequently.
And so I need the formula so that I do not have to change the VBA code everytime.

• ## Sum to start to end of the row

When you click on the command button the total is taken horizontally and vertically and written in their proper column.
for Vertical total is taken from D7:D9 till the end of the column.
For horizontal total is taken from D7:O7 for all 3 rows.

If you now insert a row then the total is taken from D8:D10 where as I require from D7:D10
Same if you insert a column.

If you delete the column for formula should also change accordingly.

The rows and columns changes frequently so the last row and column should be taken into account

I hope the explanation is clear

• ## sum start to end of the row

Re: sum start to end of the row

When you click on the command button the total is taken horizontally and vertically and written in their proper column.
for Vertical total is taken from D7:D9 till the end of the column.
For horizontal total is taken from D7:O7 for all 3 rows.

If you now insert a row then the total is taken from D8:D10 where as I require from D7:D10
Same if you insert a column.

If you delete the column for formula should also change accordingly.

The rows and columns changes frequently so the last row and column should be taken into account

I hope the explanation is clear

• ## sum start to end of the row

Re: sum start to end of the row

will attach in a day as i have to create a sample file

• ## sum start to end of the row

I have made a VBA code that calculates the total for the 3 rows.

I am unable to rectify the code that calculates total from start to end this is because when the row increases the formula should also be adjusted accordingly.

Code
``````ws1.Range("D" & ws1.Cells(Rows.Count, "B").End(xlUp).Row + 2).Resize(, ws1.Cells(5, Columns.Count).End(xlToLeft).Column - 3).FormulaR1C1 = "=SUM(R[-4]C:R[-2]C)"
ws1.Range("P7:P" & ws1.Cells(Rows.Count, "B").End(xlUp).Row).FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"``````
• ## Remove Duplicates

Re: Remove Duplicates

Your code works perfectly fine but small error pops up.

I have created a macro wherein list of all items in particular month is copied.
Wherever there are 2 or more items your code works fine. Even when no items is present the code works fine.

But in certain cases where there are only 1 item debug error pops up.

• ## Remove Duplicates

I have been searching the post for removing duplicates for long time but could not find it.

Could anyone suggest only one line for removing all duplicate entries from column B7 to end of the line and adjusting the spaces accordingly.